Subject Re: [Firebird-Architect] Re: Nulls in CHECK Constraints
Author Alex Peshkov
Dmitry Yemanov wrote:
>>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.
> OK. We also have a bugreport that check constraints should perform in the
> system context, i.e. work as "dirty read" (like our PK and FK behave). I
> don't have a clue how it corresponds to the SQL spec.

Should not we clarify one aspect of cross-table constraints. Have a look:

create table t1 (f1 float);
create table t2 (f2 float check (f2 > (select sum(f1) from t1)));

-- avoid nulls - I don't have Dmitry's HEAD :)
insert into t1 values(0);

-- that's OK - constraint violated
insert into t2 values(-1);
Statement failed, SQLCODE = -297
Operation violates CHECK constraint INTEG_240 on view or table T2
-At trigger 'CHECK_241'

-- successfully add record
insert into t2 values(1);

-- now constraint becomes violated, but (as supposed) no error displayed
insert into t1 values(100);

No need to say, that next
insert into t2 values(1);
fires an error.

Is it up to standard that enabling cross-table check constraints makes
it virtually impossible to strictly follow them?