> 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)));
> commit;
>
> -- 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?

Cross-table constraints would be working - and usuable - if Firebird
supported deferred constraints.

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com