Andreas Degert <ad@papyrus-gmbh.de> writes:
> with V7.02, it seems when a constraint evalutes to 'null', it behaves
> like 'true'. I'm rather sure this behaviour changed from V6.x, though I
> can't check it.
Yes, it did change. The previous behavior was not compliant with SQL92:
4.10.2 Table constraints
A table constraint is either a unique constraint, a referential constraint or a table check constraint.
[snip ] A table check constraint is satisfied if and only if the specified <search condition> is not
falsefor any row of a table.
"Not false" is the spec's way of saying "true or unknown (ie, NULL)".
It's not particularly consistent with the behavior of WHERE clauses,
wherein NULL is treated like FALSE:
7.6 <where clause>
1) The <search condition> is applied to each row of T. The result of the <where clause> is a table of
thoserows of T for which the result of the <search condition> is true.
Note the difference in wording. "true" and "not false" are not the same
thing in 3-valued boolean logic.
> Is this the intended behaviour?
Well, it does mean that you can put on a constraint like "X > 0" without
automatically requiring X to be non-null, as it did in our earlier code.
If you also want to constrain X to be non-null, you can specify NOT NULL
along with the constraint clause. So it's more flexible this way. Or
at least I suppose that was the SQL committee's reasoning.
regards, tom lane
