Hi,

> > 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).

Yes, this applies to Firebird.

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

Except... with the current CHECK CONSTRAINT implementation,
you cannot guarantee, for example, that only a single unique row
exists for a certain condition if you're using "read committed"
isolation when inserting a row. Why? Because the CHECK triggers
cannot read data that hasn't been committed yet. So two clients
can both insert a row that meets the check, commit and you will
have a useless check constraint in your database.

IMO, a CHECK constraint is there to prevent such things. It's
"just another" way of keeping your database in a consistent state.

And yes, I know the current implementation doesn't allow this,
but that does not mean it shouldn't be discusses for a future date :-)


With regards,

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