views:

1653

answers:

4

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

+1  A: 

Try using NVL in the check condition.

EddieAwad
+1  A: 

I'm not sure why the compound check isn't working, but this works:

ALTER TABLE CLIENTS ADD CONSTRAINT CHK_1 CHECK (IS_DISABLED = 0 AND DISABILITY_INCOME_TYPE_ID IS NULL)

ALTER TABLE CLIENTS ADD CONSTRAINT CHK_2 CHECK (IS_DISABLED IS NULL AND DISABILITY_INCOME_TYPE_ID IS NULL)

ALTER TABLE CLIENTS ADD CONSTRAINT CHK_3 CHECK (IS_DISABLED = 1)

Regards K

Khb
+4  A: 

While I do not have Oracle, I did a quick test with PostgreSQL and your first example (IS_DISABLED being NULL and DISABILITY_INCOME_TYPE_ID being 1):

postgres=> select (null is null and 1 is null);
 ?column?
----------
 f
(1 registro)

postgres=> select (null is null and 1 is null) or (null = 0 and 1 is null);
 ?column?
----------
 f
(1 registro)

postgres=> select (null is null and 1 is null) or (null = 0 and 1 is null) or (null = 1);
 ?column?
----------

(1 registro)

Here we see clearly that, in this case, your expression (at least on PostgreSQL) returns NULL. From the manual,

[...] Expressions evaluating to TRUE or UNKNOWN succeed. Should any row of an insert or update operation produce a FALSE result an error exception is raised and the insert or update does not alter the database. [...]

So, if Oracle behaves the same as PostgreSQL, the check constraint would pass.

To see if this is the case, avoid the NULL shenanigans by explicily checking for it and see if it works:

CHECK ((IS_DISABLED IS NULL AND DISABILITY_INCOME_TYPE_ID IS NULL)
    OR (IS_DISABLED IS NOT NULL AND IS_DISABLED = 0 AND DISABILITY_INCOME_TYPE_ID IS NULL)
    OR (IS_DISABLED IS NOT NULL AND IS_DISABLED = 1));
CesarB
I'm not sure your answer is correct yet, but your explanation was extremely helpful in me solving this problem on my own. This whole time I was evaluation null = 1 as false, when in fact Oracle and Postgres evaluate this expression as unknown. Major difference.
BacMan
A: 

This solution works.

CHECK
((IS_DISABLED IS NULL AND NVL(DISABILITY_INCOME_TYPE_ID, 0) = 0)
OR (IS_DISABLED = 0 AND NVL(DISABILITY_INCOME_TYPE_ID, 0) = 0) 
OR (IS_DISABLED IS NOT NULL AND IS_DISABLED = 1));
BacMan