I've been struggling with this check constraint for a few hours and was hoping someone would be kind enough to explain why this check constraint isn't doing what I think it should be doing.
ALTER TABLE CLIENTS
add CONSTRAINT CHK_DISABILITY_INCOME_TYPE_ID CHECK ((IS_DISABLED IS NULL AND DISABILITY_INCOME_TYPE_ID IS NULL) OR (IS_DISABLED = 0 AND DISABILITY_INCOME_TYPE_ID IS NULL) OR (IS_DISABLED = 1));
Essentially, you must be disabled to collect disability income. It appears as though the first part of this check constraint (IS_DISABLED IS NULL AND DISABILITY_INCOME_TYPE_ID IS NULL)
is not enforced (see below).
The available values for DISABILITY_INCOME_TYPE_ID
are 1 and 2, which is enforced via foreign key. Both IS_DISABLED
and DISABILITY_INCOME_TYPE_ID
can be null.
-- incorrectly succeeds (Why?)
INSERT INTO CLIENTS (IS_DISABLED, DISABILITY_INCOME_TYPE_ID) VALUES (null, 1);
INSERT INTO CLIENTS (IS_DISABLED, DISABILITY_INCOME_TYPE_ID) VALUES (null, 2);
-- correctly fails
INSERT INTO CLIENTS (IS_DISABLED, DISABILITY_INCOME_TYPE_ID) VALUES (0, 1);
INSERT INTO CLIENTS (IS_DISABLED, DISABILITY_INCOME_TYPE_ID) VALUES (0, 2);
-- correctly succeeds
INSERT INTO CLIENTS (IS_DISABLED, DISABILITY_INCOME_TYPE_ID) VALUES (0, null);
INSERT INTO CLIENTS (IS_DISABLED, DISABILITY_INCOME_TYPE_ID) VALUES (1, 1);
INSERT INTO CLIENTS (IS_DISABLED, DISABILITY_INCOME_TYPE_ID) VALUES (1, 2);
INSERT INTO CLIENTS (IS_DISABLED, DISABILITY_INCOME_TYPE_ID) VALUES (1, null);
INSERT INTO CLIENTS (IS_DISABLED, DISABILITY_INCOME_TYPE_ID) VALUES (null, null);
Thanks for your help, Michael