oracle+constraint后面,Oracle Check Constraint

虽然我没有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));