Oracle检查约束

Question

几个小时以来，我一直在为这个检查约束而苦苦挣扎，希望有人能好心解释一下为什么这个检查约束没有做我认为它应该做的事情。

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

从本质上讲，你必须是残疾人才能获得残疾收入。看起来好像这个检查约束(IS_DISABLED IS NULL AND DISABILITY_INCOME_TYPE_ID IS NULL)的第一部分并没有被强制执行(见下文)。

DISABILITY_INCOME_TYPE_ID的可用值为1和2，通过外键强制执行。IS_DISABLED和DISABILITY_INCOME_TYPE_ID都可以为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);

谢谢你的帮助，迈克尔

Answer 1

虽然我没有Oracle，但我用PostgreSQL和您的第一个示例(IS_DISABLED为NULL，DISABILITY_INCOME_TYPE_ID为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)

在这里我们清楚地看到，在这种情况下，您的表达式(至少在PostgreSQL上)返回NULL。来自the manual，

...计算为TRUE或UNKNOWN的表达式成功。如果insert或update操作的任何行产生假结果，则会引发错误异常，并且insert或update操作不会更改数据库。..。

因此，如果Oracle的行为与PostgreSQL相同，那么检查约束将通过。

要查看是否是这种情况，请通过明确地检查它并查看它是否有效来避免NULL恶作剧：

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

Answer 2

尝试在检查条件中使用NVL。

Answer 3

我不确定为什么复合检查不起作用，但这是有效的：

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)

关于K