我一直在努力解决这个检查约束几个小时,并希望有人能够解释为什么这个检查约束没有做我认为它应该做的事情.
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);
谢谢你的帮助,
迈克尔
解决方法
虽然我没有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 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));
