 |
|
| Computers Forum Index » Computer - Databases - IBM DB2 » Doubts about CHECK CONSTRAINT... |
|
Page 1 of 1 |
|
| Author |
Message |
| Damir... |
 Posted: Fri Nov 06, 2009 6:16 pm |
|
|
|
Guest
|
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 at (no spam)
A B
- -
- 2
1 record(s) selected. |
|
|
| Back to top |
|
|
|
| Lennart... |
| Posted: Fri Nov 06, 2009 6:16 pm |
|
|
|
Guest
|
On Nov 6, 3:08 pm, "Damir" <damirwil... at (no spam) yahoo.com> wrote:
Quote: 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 |
|
|
| Back to top |
|
|
|
| Tonkuma... |
| Posted: Fri Nov 06, 2009 6:16 pm |
|
|
|
Guest
|
Quote: select * from tmp.test_03 at (no spam)
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. |
|
|
| Back to top |
|
|
|
| Damir... |
| Posted: Fri Nov 06, 2009 7:08 pm |
|
|
|
Guest
|
Quote: 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 |
|
|
| Back to top |
|
|
|
| Tonkuma... |
| Posted: Sat Nov 07, 2009 5:59 am |
|
|
|
Guest
|
Quote: 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. |
|
|
| Back to top |
|
|
|
| Lennart... |
| Posted: Sat Nov 07, 2009 7:12 am |
|
|
|
Guest
|
On 7 Nov, 06:59, Tonkuma <tonk... at (no spam) fiberbit.net> wrote:
[...]
Quote:
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 |
|
|
| Back to top |
|
|
|
| Damir... |
| Posted: Mon Nov 09, 2009 3:30 pm |
|
|
|
Guest
|
Thanks for clearing out this one!
Damir
--
Quote: My way to remember three valued logic in SQL is .....
|
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Thu Dec 10, 2009 9:12 pm
|
|