Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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));
- -- 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);
- 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)
- 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));
- 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)
- 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));
Add Comment
Please, Sign In to add comment