Doubts about CHECK CONSTRAINT
Damir
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.
Tonkuma
>
> 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.
Damir
OK, but it also says that "if A is null, then B also has to be null"?!
Damir
Lennart
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
Tonkuma
> 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.
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).
>
Ah, that's interesting. I never thought of that. However I usually
just use
TRUE and UNKOWN : UNKOWN
TRUE or UNKNOWN : TRUE (doesn't matter what unknown is)
FALSE and UNKNOWN : FALSE (doesn't matter what unknown is)
FALSE or UNKNOWN : UNKNOWN
together with the normal rules (and op(null, null)) that apply for 2
values logic.
Even though three valued logic in it self adds much complexity, there
is something worse lurking around (IMO). In many data models nulls
sometime mean unknown, sometime non existing, sometime .... Depending
on the meaning of null one must use different approaches when querying
for data.
It is easy enough to construct strange things with the help of null. A
few examples that comes to mind:
CREATE TABLE T (
A INT,
B INT
);
INSERT INTO T (A,B)
VALUES (null,null), (1,null), (null,1), (1,1);
Example 1
select sum(A)+sum(B) as X, sum(A+B) as Y from T;
4 2
Example 2
select T1.A, T1.B from T T1, T T2
where T1.A = T2.A and T1.B = T2.B;
1 1
select T1.A, T1.B from T T1
intersect
select T2.A, T2.B from T T2;
1 1
- 1
1 -
- -
Exempel 3
select T1.A, T1.B from T T1
where T1.A not in (select T2.A from T T2 where T2.A = 1);
Empty set
select T1.A, T1.B from T T1
where not exists (
select T2.A from T T2
where T2.A = 1 and T1.A = T2.A
);
- -
- 1
Etc.
IMO, it is difficult to avoid nulls in the data model at all times,
but I get a feeling that they are often introduced as a result of old
habits rather than necessity.
/Lennart
Damir
Damir
--