> * Documentation clearly states that CHECK constraint should check
> values only from curent row. From this point of view this thread is
> about misusing the feature, not about bug.

Well then... Time for the ability to create "database constraints"
and not only "current row constraints".

> * If somebody still thinks that it is a bug, he should clearly state
> what the solution/correct behaviour should be.
> So far I only read that CHECK should fire
> - before operation, -after operation, -on commit,
> but *none* of these options will solve the "problem".
> (even if Firebird fires all of them)
>
> Think about this simple example:
>
> CREATE TABLE T (
> I INTEGER CHECK (I = (SELECT COUNT(*) FROM T)) );
>
> INSERT INTO T VALUES (0);
> COMMIT;
> INSERT INTO T VALUES (1);
>
> The second insert will invalidate CHECK constraint for previously
> inserted and committed value. How do you want to prevent this ?
> Probably by always checking *whole* table for each invoked CHECK ???
>
> Does SQL standard really says that CHECK should be satisfied permanently,
> and not only during the operation ?

Deferred constraints should keep the database in a consistent
state at the time of COMMIT. So yes.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com