Ограничение проверки Oracle
Я боролся с этим ограничением проверки в течение нескольких часов и надеялся, что кто-нибудь будет достаточно любезен, чтобы объяснить, почему это ограничение проверки не выполняет то, что, по моему мнению, должно делать.
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);
Спасибо за вашу помощь, Майкл
Ответы 4
Попробуйте использовать NVL в условии проверки.
Я не уверен, почему комплексная проверка не работает, но это работает:
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
Хотя у меня нет 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. От руководство,
[...] 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));
Я еще не уверен, что ваш ответ правильный, но ваше объяснение чрезвычайно помогло мне решить эту проблему самостоятельно. Все это время я оценивал null = 1 как ложь, тогда как на самом деле Oracle и Postgres оценивают это выражение как неизвестное. Главное отличие.
Это решение работает.
CHECK
((IS_DISABLED IS NULL AND NVL(DISABILITY_INCOME_TYPE_ID, 0) = 0)
OR (IS_DISABLED = 0 AND NVL(DISABILITY_INCOME_TYPE_ID, 0) = 0)
OR (IS_DISABLED IS NOT NULL AND IS_DISABLED = 1));
Просто проверяю, что я наблюдаю то же поведение, что и в Oracle 10.2.0.4.0.