Subject Re: [firebird-support] Re: Possible bug with CHECK constraint
Author Ann W. Harrison
At 07:56 AM 10/3/2004, Roman Rokytskyy wrote:

>It is perfectly clear why I was able to insert id=20 - constraint was
>not there.

You would have been able to insert id=20 even if the constraint
were present. The standard says that immediate constraints should
be evaluated after the action, not before, so a standard compliant
system would reject your update. Firebird evaluates constraints
before the action, not after.

>- when I add UNIQUE constraint for a table, table is scanned for the
>duplicates and operation fails when a duplicate is found (I suspect
>that happens when unique index is created, not when constraint is added);

Yes that's right.

>- when I add CHECK constraint for a table, constraint is simply added
>and nothing fails (no index is created, nothing to fail).

That is the long-standing behavior of Firebird and goes back to
my husband's belief that bad things should happen to stupid lazy
people. We've discussed this at length over nearly thirty years,
and have come to agreement that bad things will happen to stupid,
lazy people. We disagree about whether it's up to Firebird to
make the bad things happen.

My ancient SQL Standard Document does not address the question
of whether constraints should be evaluated when they are added
to tables or domains. Perhaps someone with a more recent version
will explain the contemporary rules.

> >From my point of view second case is a bug. My edition of C.J.Date
>does not define what should happen when constraints are added to the
>existing data, but I think Firebird should be consistent - either all
>integrity constraints should be cheched before they are added or none
>of them.

Actually, the old SQL standard distinguishes between types of
constraints - unique constraints, table constraints, domain
constraints - in a way that might allow them to behave differently
when defined, since that issue was left up to the implementation.
I'm not saying that's good, just that it might once have been

As an aside, now that PDF versions of the standard are available
for $18/volume I really ought to get myself a copy. And so should
you, all of you.