Check constraint across parent-child tables

[Doug Baroter]

Hi,

DDLs and DMLs:
create table #job (jobID int identity(1,1) primary key, jobName
varchar(25) unique not null, jobEndDate dateTime, jobComplete bit
default(0), check (([JobEndDate] is null and [JobComplete] = 0) OR
([JobEndDate] is not null and [JobComplete] = 1)));

Q1 with check constraint:
sample dagta
insert into #job (jobName)
values('first job');

transaction A
update #job
set jobEndDate = '12/19/2003', JOBCOMPLETE=1
where jobID = 3;
RESULTSET/STATUS = Success

update #job
set jobEndDate = NULL, JOBCOMPLETE=0
where jobID = 3;
RESULTSET/STATUS = Success

transaction C
update #job
set jobEndDate = '12/19/2003'
where jobID = 3;
RESULTSET/STATUS = Failure

how come check constraint can't set a value which is preset in the
check constraint? If it's the way how it works with MS SQL Server
2000, well, IMHO, it's limiting because the above transaction C is a
valid one. Or maybe check constraint is not fit for this purpose?

Maybe, it doesn't make much sense for me to go into Q2 but I'll try
-- create job's child table, task
create table #task (taskID int identity(1,1) primary key, taskName
varchar(25) unique not null, taskEndDate dateTime, taskComplete bit
default(0), jobID int not null references #job (jobID));
-- skip check constraint for taskEndDate and taskComplete for now

Now, the Business Rule says,
1) if all tasks are complete then automatically set jobComplete for
the #job table to yes;
2) the jobEndDate in the #job table must be >= the last/Max
taskEndDate

I tend to think trigger would slow down data update quite a bit, so,
try to stay away for this purpose if possible.

Always appreciate your idea.

[David Portas]

> how come check constraint can't set a value which is preset in the

check constraint? If it's the way how it works with MS SQL Server
As I said in my previous answer to a related question from you, the purpose
of a CHECK constraint is to *validate* your data and raise an error if it is
invalid. A CHECK constraint cannot change values in your data. Use a trigger
for that. This is the way CHECK constraints work in standard SQL (although
SQL Server doesn't support full ANSI SQL constraints with subqueries).

Now, the Business Rule says,
1) if all tasks are complete then automatically set jobComplete for
the #job table to yes;

Then the JobComplete column in the Job table looks redundant. Good design
practice is to record a fact in your database once and once only. This
avoids UPDATE anomalies which, as you rightly say, can lead to performance
problems or to incorrect data. You can drop the JobComplete column and use a
view instead:

CREATE VIEW Job_with_completion_status
AS
SELECT J.jobid, J.jobname, J.jobenddate,
MIN(CASE taskcomplete WHEN 1 THEN 1 ELSE 0 END) AS jobcomplete
FROM Job AS J
JOIN Task AS T
ON J.jobid = T.jobid
GROUP BY J.jobid, J.jobname, J.jobenddate
--
David Portas
------------
Please reply only to the newsgroup
--

[Doug Baroter]

> As I said in my previous answer to a related question from you, the purpose

of a CHECK constraint is to *validate* your data and raise an error if it is
invalid. A CHECK constraint cannot change values in your data. Use a trigger
for that. This is the way CHECK constraints work in standard SQL (although
SQL Server doesn't support full ANSI SQL constraints with subqueries). Thanks for the clarification.

Now, the Business Rule says,
1) if all tasks are complete then automatically set jobComplete for
the #job table to yes;
Then the JobComplete column in the Job table looks redundant. Good design
practice is to record a fact in your database once and once only. This
avoids UPDATE anomalies which, as you rightly say, can lead to performance
problems or to incorrect data.

Well, I think you're exactly right from the strictest design
perspective, however, the organization decides to expose all the
columns for some of its applications to a large group of users, and
many of these users would have no idea about the simple logic the fact
that if jobCompleteDate/jobEndDate is NULL it would indicate a job is
not complete but almost all of them would understand perfectly well
when jobComplete is 0 it means the job is not done yet. Hence, slight
design compromise.
You can drop the JobComplete column and use a
view instead:

CREATE VIEW Job_with_completion_status
AS
SELECT J.jobid, J.jobname, J.jobenddate,
MIN(CASE taskcomplete WHEN 1 THEN 1 ELSE 0 END) AS jobcomplete
FROM Job AS J
JOIN Task AS T
ON J.jobid = T.jobid
GROUP BY J.jobid, J.jobname, J.jobenddate

Thanks, probably a UDF can solve the same problem as well?