Subject Re: [Firebird-Architect] Re: Nulls in CHECK Constraints
Author Ann W. Harrison
Let me recap the discussion, just for clarity.

1) According to the standard, Oracle, Postgres, and
MSSQL, a constraint stops an action if it is FALSE.
A constraint that evaluates to NULL is not FALSE, so
it does not stop the action. For example

create table t1 (f1 integer, f2 integer
check (f2 > 0));

SQL> create table t1 (f1 integer, f2
CON> integer check (f2 > 0));
SQL> insert into t1 (f1) values (1);
Statement failed, SQLCODE = -297

Operation violates CHECK constraint INTEG_1 on view or table T1

According to the standard, the insert should succeed - and,
by implication, to keep null values out of f2, it should be
declared NOT NULL.

So, the question is whether we keep a long-standing, non-standard
behavior. And, this time, my vote is no. I wouldn't make the
change in 1.5, but would in FB2 and Vulcan.

This is non-standard behavior in a SQL standard feature, which
is a bug. It's not a priority 1 bug and there are workarounds,
but it is a bug and we ought to fix it.

2) There are other issues around constraints, including
their use of other tables, an whether or not they are
invoked on delete. Here I agree with Dmitry that the
standards does not disallow cross-table constraints and
with everyone that constraint handling tends not to be
as solid as it should be. But that's for another day.