code_junkie

Oracle Check Constraint

Nov 12th, 2011
79
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.97 KB | None | 0 0
  1. ALTER TABLE CLIENTS
  2. 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));
  3.  
  4. -- incorrectly succeeds (Why?)
  5. INSERT INTO CLIENTS (IS_DISABLED, DISABILITY_INCOME_TYPE_ID) VALUES (null, 1);
  6. INSERT INTO CLIENTS (IS_DISABLED, DISABILITY_INCOME_TYPE_ID) VALUES (null, 2);
  7.  
  8. -- correctly fails
  9. INSERT INTO CLIENTS (IS_DISABLED, DISABILITY_INCOME_TYPE_ID) VALUES (0, 1);
  10. INSERT INTO CLIENTS (IS_DISABLED, DISABILITY_INCOME_TYPE_ID) VALUES (0, 2);
  11.  
  12. -- correctly succeeds
  13. INSERT INTO CLIENTS (IS_DISABLED, DISABILITY_INCOME_TYPE_ID) VALUES (0, null);
  14. INSERT INTO CLIENTS (IS_DISABLED, DISABILITY_INCOME_TYPE_ID) VALUES (1, 1);
  15. INSERT INTO CLIENTS (IS_DISABLED, DISABILITY_INCOME_TYPE_ID) VALUES (1, 2);
  16. INSERT INTO CLIENTS (IS_DISABLED, DISABILITY_INCOME_TYPE_ID) VALUES (1, null);
  17. INSERT INTO CLIENTS (IS_DISABLED, DISABILITY_INCOME_TYPE_ID) VALUES (null, null);
  18.  
  19. postgres=> select (null is null and 1 is null);
  20. ?column?
  21. ----------
  22. f
  23. (1 registro)
  24.  
  25. postgres=> select (null is null and 1 is null) or (null = 0 and 1 is null);
  26. ?column?
  27. ----------
  28. f
  29. (1 registro)
  30.  
  31. postgres=> select (null is null and 1 is null) or (null = 0 and 1 is null) or (null = 1);
  32. ?column?
  33. ----------
  34.  
  35. (1 registro)
  36.  
  37. CHECK ((IS_DISABLED IS NULL AND DISABILITY_INCOME_TYPE_ID IS NULL)
  38. OR (IS_DISABLED IS NOT NULL AND IS_DISABLED = 0 AND DISABILITY_INCOME_TYPE_ID IS NULL)
  39. OR (IS_DISABLED IS NOT NULL AND IS_DISABLED = 1));
  40.  
  41. ALTER TABLE CLIENTS ADD CONSTRAINT CHK_1 CHECK (IS_DISABLED = 0 AND DISABILITY_INCOME_TYPE_ID IS NULL)
  42.  
  43. ALTER TABLE CLIENTS ADD CONSTRAINT CHK_2 CHECK (IS_DISABLED IS NULL AND DISABILITY_INCOME_TYPE_ID IS NULL)
  44.  
  45. ALTER TABLE CLIENTS ADD CONSTRAINT CHK_3 CHECK (IS_DISABLED = 1)
  46.  
  47. CHECK
  48. ((IS_DISABLED IS NULL AND NVL(DISABILITY_INCOME_TYPE_ID, 0) = 0)
  49. OR (IS_DISABLED = 0 AND NVL(DISABILITY_INCOME_TYPE_ID, 0) = 0)
  50. OR (IS_DISABLED IS NOT NULL AND IS_DISABLED = 1));
Add Comment
Please, Sign In to add comment