| Subject | Re: [firebird-support] Re: Possible bug with CHECK constraint |
|---|---|
| Author | Ann W. Harrison |
| Post date | 2004-10-03T17:23:45Z |
At 07:56 AM 10/3/2004, Roman Rokytskyy wrote:
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.
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.
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
defensible.
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.
Cheers,
Ann
>It is perfectly clear why I was able to insert id=20 - constraint wasYou would have been able to insert id=20 even if the constraint
>not there.
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 theYes that's right.
>duplicates and operation fails when a duplicate is found (I suspect
>that happens when unique index is created, not when constraint is added);
>- when I add CHECK constraint for a table, constraint is simply addedThat is the long-standing behavior of Firebird and goes back to
>and nothing fails (no index is created, nothing to fail).
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.DateActually, the old SQL standard distinguishes between types of
>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.
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
defensible.
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.
Cheers,
Ann