Doubts about CHECK CONSTRAINT
in [DB2]
|
Prev: reorgchk_ix_stats and reorgchk_tb_stats question
Next: Help! weird Common Table Expressions behavior with SYSTEM TEMP TABLESPACE
From: Damir on 6 Nov 2009 08:16 Hello, could someone please explain why the following insert completes successfully? Tnx, Damir create table TEST1 (A char(1), B char(1)); alter table TEST1 add constraint CONSTR1 check ((A = 'D' and B is not null) or (A is null and B is null)); insert into TEST1 (B) values ('2'); select * from tmp.test_03@ A B - - - 2 1 record(s) selected.
From: Tonkuma on 6 Nov 2009 08:44 > select * from tmp.test_03@ > > A B > - - > - 2 > > 1 record(s) selected. CHECK (check-condition) Defines a check constraint. The search-condition must be true or unknown for every row of the table. If A is null, then "A = 'D'" is unknown.
From: Damir on 6 Nov 2009 09:08 > If A is null, then "A = 'D'" is unknown. OK, but it also says that "if A is null, then B also has to be null"?! Damir
From: Lennart on 6 Nov 2009 10:14 On Nov 6, 3:08 pm, "Damir" <damirwil...(a)yahoo.com> wrote: > > If A is null, then "A = 'D'" is unknown. > > OK, but it also says that "if A is null, then B also has to be null"?! > > Damir Nulls causes much confusion. Let's see what happens in the evaluation of your check constraint: CHECK ( (null = 'D' and '2' is not null ) or (null is null and '2' is null) ) <=> CHECK ( (null and TRUE ) or (TRUE and FALSE) ) <=> CHECK ( (null) or (FALSE) ) <=> CHECK ( null ) which is ok Like Tonkuma says, the problem lies in null = 'D' -> null. You can prevent this by changing part1 to: A is not null and A = 'D' and B is not null Let's see now: CHECK ( (null is not null and null = 'D' and '2' is not null ) or (null is null and '2' is null) ) <=> CHECK ( (FALSE and null and TRUE) or (TRUE and FALSE) ) <=> CHECK ( (FALSE) or (FALSE) ) <=> CHECK ( FALSE ) HTH /Lennart
From: Tonkuma on 7 Nov 2009 00:59
> Nulls causes much confusion. Let's see what happens in the evaluation > of your check constraint: > > CHECK ( (null = 'D' and '2' is not null ) or > (null is null and '2' is null) ) > <=> > CHECK ( (null and TRUE ) or > (TRUE and FALSE) ) > <=> > CHECK ( (null) or > (FALSE) ) > <=> > CHECK ( null ) > > Let's see now: > > CHECK ( (null is not null and null = 'D' and '2' is not null ) or > (null is null and '2' is null) ) > <=> > CHECK ( (FALSE and null and TRUE) or > (TRUE and FALSE) ) > <=> > CHECK ( (FALSE) or > (FALSE) ) > <=> > CHECK ( FALSE ) > > HTH > /Lennart My way to remember three valued logic in SQL is ..... 1) Assign nubbers(0,1,2) to Logical value Logical Value(P) : Number(p) True : 2 Unknown : 1 False : 0 2) Take MIN(p, q) for P AND Q, MAX(p, q) for P OR Q, and 2 - p for NOT (P). Table 29. Truth Tables for AND and OR P p | Q q | P AND Q | P OR Q | | | MIN(p,q)| MAX(p,q)| ----------+-----------+-----------+-----------+ True 2 | True 2 | True 2 | True 2 | True 2 | False 0 | False 0 | True 2 | True 2 | Unknown 1 | Unknown 1 | True 2 | False 0 | True 2 | False 0 | True 2 | False 0 | False 0 | False 0 | False 0 | False 0 | Unknown 1 | False 0 | Unknown 1 | Unknown 1 | True 2 | Unknown 1 | True 2 | Unknown 1 | False 0 | False 0 | Unknown 1 | Unknown 1 | Unknown 1 | Unknown 1 | Unknown 1 | P p | NOT(P) | | 2 - p | ----------+-----------+ True 2 | False 0 | False 0 | True 2 | Unknown 1 | Unknown 1 | This is one example. There must be other ways to remember Three valued logic. |