> Again, please. Do you say that Firebird checks the constraint
> before the INSERT when the table is perfectly consistent, then
> it allows INSERT without checking the values, etc. making the
> database inconsistent (e.g. any update or insert that does not
> fix the problem will fail even when data are perfectly correct)?

I do not quite understand what the problem is here. Regardless
of what the standard says, it has been explained that CHECK
constraints are applied before the insert occurs (but after the
"before" triggers have executed).

This is an example of "working as designed" (and has been this
way for a very long time). Whether you agree with it or not,
it quite simply "is" - and changing it now could break existing
applications.

All this means is that the logic of your constraint needs to
take the situation into account, its not difficult.

IMO the FB way makes sense. When I write my own integrity
constaints in triggers I do so (as far as possible) in the
before triggers. Why would I want an insert to complete and
all the indexes etc to be updated, only to need all that work
undone when I belatedly discover the new row does not fit?
What a mess!

To do the check after insert is like bending something until
it breaks - and then having to glue the pieces back together.
Sometimes the situation may force us to do this - in which
case we write after insert triggers to perform the check.

> Would you or Helen write a document describing how
> constraints work? Or maybe it is already in Helen's book?

The book has quite a lot to say about check constraints. In
particular there is a caution about exactly what you are doing
here, trying to check a value against other rows in the same
(or different) table - because changes impacting the check may
be occuring in other transactions.


I presume that the example check constraint you gave was
contrived only to demonstrate the issue. Such an approach
(reading through the entire table with every insert or update
to check for a problem) is surely not desireable - especially
in a situation where a simple column constaint will work.


The issue of whether creation of new constraints should ensure
that existing data passes the new constraint is quite separate.
Personnally I would prefer that it did ensure validity. The
"bad things to stupid lazy people" does not do it for me, I
dont think I am stupid but I certainly can be lazy - what I
dont expect is for my dbms to be lazy, particularly when it
may lead to broken data integrity.

--
Geoff Worboys
Telesis Computing