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

13 Sep 2000 10:44:46 +0200
Andreas Degert <ad@papyrus-gmbh.de>
null in constraints
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 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

Wed, 13 Sep 2000 11:10:11 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: null in constraints 