qw********@boxfrog.com (Doug Baroter) wrote in message news:<fc**************************@posting.google. com>...
Hi,
DDL:
-- create table #task (taskID int identity(1,1) primary key, taskName
varchar(25) unique, taskCompleteDate dateTime, taskComplete bit
default(0));
/*
Business Rules:
a) if taskCompleteDate is NULL (like default) then, taskComplete may
not be set to 1 (attempt to update it to 1 would fail);
b) else automatically set taskComplete = 1
*/
I was thinking using CHECK constraint (mutual constraint if possible),
along the following line:
CHECK (if taskCompleteDate is null set taskComplete=0 else set
taskComplete=1)
Hmm, your thought?
Thanks.
You could implement the CHECK constraint like this:
create table #task (
taskID int identity(1,1) primary key,
taskName varchar(25) unique,
taskCompleteDate dateTime,
taskComplete bit default(0),
constraint CHK_taskComplete check ((taskCompleteDate is null and
taskComplete = 0) or (taskCompleteDate is not null and taskComplete =
1))
);
Or use a computed column:
create table #task (
taskID int identity(1,1) primary key,
taskName varchar(25) unique,
taskCompleteDate dateTime,
taskComplete as case when taskCompleteDate is not null then 1 else 0
end
);
The taskComplete flag seems to be redundant, given that you can use
taskCompleteDate for the same purpose, but presumably there's a reason
for this in your environment.
Simon