Thread: null in constraints

Hello Hackers,

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.

example:
 create table test (a int4 check (a > 0));

allows a to be null. But
 select * from test where a > 0;

doesn't return any null values.

Is this the intended behaviour?

cheers
Andreas

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