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 都可以为空。

-- 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 ,

[...] 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. [...]

因此，如果 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));