> From: "Ann W. Harrison"
> At 11:40 AM 10/4/2004, Ivan Prenosil wrote:
>
> > > CREATE TABLE test_master (
> > > id INTEGER NOT NULL PRIMARY KEY,
> > > CHECK(COALESCE((SELECT max(id) FROM test_master), 0) < 10)
> >
> >So, even if Firebird supported sql92 standard, would that solve
> >the problem ?
>
> If that constraint were evaluated after the record was stored,
> it would work as expected. And, of course, be really expensive
> compared to the simple "id < 10" test that we would normally
> use.

Everybody has different opinion about what does "as expected" mean.
In the context of this thread it means that "check constraint should be valid
after commit, for the whole database" (my personal opinion is different).

The example again:

CREATE TABLE T ( I INTEGER CHECK(I >= (SELECT COUNT(*) FROM T)) );
INSERT INTO T(I) VALUES (1);
COMMIT;
INSERT INTO T(I) VALUES (2);
COMMIT;

If the check constraint for the last insert is evaluated before insert,
after insert, before commit, it is still valid. However the table now contains
values 1,2, and count(*) is 2, which means that first inserted value (1)
violates the constraint. What am I missing ? Should check constraint
be always evaluated for the whole table/database instead of current row ?

Ivan