> 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?
>

a more concrete example I can think of is:

Supplier(SupplierID, SupplierName, SupplierType)

Part(PartNo, PartName)

SupplierPart(SupplierID, PartID, UnitPrice)

Constraint is "supplier of type A cannot supply a part that costs more
than x amount"

Changing supplier type, or modifying a part unit price can potentially
violate that constraint. Without the support for database-wide
constraints, and with the current implementation of check constraints,
this would necessitate duplicating the constraint on both Supplier and
SupplierPart tables (not good)

Also, how would you enforce a constraint "supplier of type B must
supply at least y number of parts"? This would require the constraint
be both defined at Supplier and SupplierPart tables and be checked on
delete on the SupplierPart table (aside from the usual insert, update)

Of course all these issues have already been covered in public
literature, particularly those by Date, et al. I really like Date's
classification of constraints in a relational database, much better
than the SQL view of it, although I'm not sure how
elegant-but-non-standard should be evaluated against
standard-but-kludgy in Firebird.

(Note, I'm writing this a few minutes after waking up so I apologize
for any minor errors)