Any possibility of creating a MUTUALLY dependent CHECK

[Doug Baroter]

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.

[David Portas]

Why not just eliminate the transitive dependency? The taskcomplete column is
redundant unless you wanted to allow a case where taskcomplete = 1 but the
date is NULL (you know the task is complete but the date is unknown). Since
your business rule excludes that case I would just put the taskcomplete
column in a view:

CREATE SomeView AS
SELECT ... taskcompletedate, CASE WHEN taskcompletedate IS NULL
THEN 1 ELSE 0 END, ...

Also, why so many nullable columns? Aparently you don't have a natural
primary key (should it be Taskname?).

In answer to your question, yes you can create a mutually dependent
constraint, but the purpose of a constraint is to validate data not to
change it:

.... taskcompletedate DATETIME, taskcomplete BIT NOT NULL DEFAULT 0, CHECK
((taskcompletedate IS NULL AND taskcomplete=0) OR (taskcompletedate IS NOT
NULL AND taskcomplete=1))...

--
David Portas
------------
Please reply only to the newsgroup
--

[Simon Hayes]

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

[Doug Baroter]

Thanks. I was losing the link between "check" and "contraint" :)

sq*@hayes.ch (Simon Hayes) wrote in message news:<60**************************@posting.google. com>...

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

[Doug Baroter]

"why so many nullable columns?" you're right.
It's not an actual table, just to get a point across for the question. Thanks.

"David Portas" <RE****************************@acm.org> wrote in message news:<Gf********************@giganews.com>...

Why not just eliminate the transitive dependency? The taskcomplete column is
redundant unless you wanted to allow a case where taskcomplete = 1 but the
date is NULL (you know the task is complete but the date is unknown). Since
your business rule excludes that case I would just put the taskcomplete
column in a view:

CREATE SomeView AS
SELECT ... taskcompletedate, CASE WHEN taskcompletedate IS NULL
THEN 1 ELSE 0 END, ...

Also, why so many nullable columns? Aparently you don't have a natural
primary key (should it be Taskname?).

In answer to your question, yes you can create a mutually dependent
constraint, but the purpose of a constraint is to validate data not to
change it:

... taskcompletedate DATETIME, taskcomplete BIT NOT NULL DEFAULT 0, CHECK
((taskcompletedate IS NULL AND taskcomplete=0) OR (taskcompletedate IS NOT
NULL AND taskcomplete=1))...

[Doug Baroter]

Oops, I did a slightly different implementation on an existing table,
so, I created a check constraint via EM, the contraint reads:
([JobEndDate] is null and [JobComplete] = 0 OR [JobEndDate] is not
null and [JobComplete] = 1)

/*
consider job in place of task, thanks;
I thought with the check constraint, if JobEndDate is updated with a
value then JobComplete column would be automatically updated with
value of 1, didn't seem to work this way
*/

Attempt to update JobEndDate failed due to the constraint, err msg:
UPDATE statement conflicted with TABLE CHECK constraint
'CK_JobDateStatus'

Appreciated.

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))
);