Oracle Check Constraint

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为空且DISABILITY_INCOME_TYPE_ID为空)(参见下文).

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

-- correctly fails
INSERT INTO CLIENTS (IS_DISABLED,DISABILITY_INCOME_TYPE_ID) VALUES (0,2);

-- correctly succeeds
INSERT INTO CLIENTS (IS_DISABLED,null);
INSERT INTO CLIENTS (IS_DISABLED,DISABILITY_INCOME_TYPE_ID) VALUES (1,2);
INSERT INTO CLIENTS (IS_DISABLED,null);

谢谢你的帮助,
迈克尔

解决方法

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 shenanigans：

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!