Re: null in constraints

Andreas Degert <ad(at)papyrus-gmbh(dot)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 false for 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 those rows 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