Peter,

1. CHECK constraints implicitly forbid NULL when not

>overriden (SQL standard says the other way around, if I'm
>not mistaken).

You are not mistaken, but the language that defines that almost
defies analysis.

If the <table constraint> is a <check constraint definition>,
then let SC be the <search condition> immediately contained in the
<check constraint definition>
and let T be the table name included in the corresponding table
constraint descriptor;
the table constraint is not satisfied if and only if
EXISTS ( SELECT * FROM T WHERE NOT ( SC ) )
is true.

So, assuming a table foobar with fields f2 and
a constraint saying "f2 = 'a'" then values are ok as
long as this statement:

select "invalid"
from rdb$database
where exists (select * from foobar where not (f2 = 'a'));

must return no value. Which it does even if there is an f2
in the table that is NULL.

Which is another of the things that drives me nuts about the
SQL standard. They've got a double standard for NULL and they
hide it under this triple negative non-sense.


Regards,

Ann
www.ibphoenix.com
We have answers.