CODEWITHSUNDEEP
oracle-databaseconstraints
BacMan
BacMan

Reputation: 436

Oracle Check Constraint

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

Upvotes: 3

Views: 4562

Answers (4)

BacMan
BacMan

Reputation: 436

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

Upvotes: 0

CesarB
CesarB

Reputation: 45595

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

Upvotes: 5

Khb
Khb

Reputation: 1433

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

Upvotes: 1

Eddie Awad
Eddie Awad

Reputation: 3739

Try using NVL in the check condition.

Upvotes: 1

Related Questions