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

...

> What am I missing ? Should check constraint be always
> evaluated for the whole table/database instead of current row ?

I know that "me too" postings are frowned upon but... me too!
:-)

I happen to really like the example. It demonstrates the
potential complexity of cross-row constraints without resorting
to multiple interacting transactions or multiple tables.

The fact is that a single constraint syntax is not sufficient
to provide differentiation between static (whole table) and
dynamic (before or after individual action) constraint
requirements.

Although this gets a bit off-topic from the original question
of whether a constraint should be checked before or after the
action - the main justification for after action evaluation
seems to be related to cross-row or whole table evaluation.

Constraints that rely on values in other rows are inherently
complex. Which rows, which tables, what conditions? At this
point I see no way to express such complexity simply - while
still retaining one of Firebirds important features, non-
blocking updates.

Constraints such as the example above (and many of the other
examples provided so far) would require the entire table locked
to other updates. Such locking would either have to happen
immediately - leaving the potential for long standing locks
over a large number of rows - or tracked until commit and then
locked for the duration of the commit process - leaving
potential for huge overheads in transactions and, as pointed
out earlier, leaving data in tables (visible to the current
transaction) that may or may not meet the defined constraint
conditions.

This gets really really messy! Better to leave such weird and
wonderful requirements to trigger code and similar application
specific implementations.

--
Geoff Worboys
Telesis Computing