While I agree that true database constraints would be good, and deferred constraints make sense,
this also means that statements/triggers/procedures can no longer trust that the data being
processed will match the constraints. So long as you're in the middle of a transaction, all bets
are off. It's at least something people should be warned about.

The other thing is locking. If FK's are only checked at commit-time, is there any way we could
avoid the "referenced record is locked for update" issue without work-arounds? I've coded my app so
it politely informs the user of this, but it's really annoying to have a vague message like "you
can't edit this attached address because someone, somewhere, seems to be updating the person's
information" (and it's usually much, much more vague.) We don't allow delete (not by users,
anyway), so the reasoning of "the record might be deleted" is correct, but unlikely. If it were
deleted, sure, I'd want the update on addresses to fail (besides, there's likely a cascade delete
involved.) But under most circumstances, I'd want to favor multi-user cooperation as much as
possible. It seems like deferred constraints could help with this. ("Yes, the other user saved
changes to the person. No, the person was not deleted. Yes, it was okay for you to make changes to
the attached address.")

[And yeah. It'd be really nice to be able to know -why- a constraint failed, who the other
transaction(s) are that caused the deadlock, etc.]

Thoughts on either issue?
-Philip

---------- Original Message -----------
From: "Martijn Tonies" <m.tonies@...>
To: <firebird-support@yahoogroups.com>
Sent: Tue, 5 Oct 2004 08:04:39 +0200
Subject: Re: [firebird-support] Possible bug with CHECK constraint

> > * Documentation clearly states that CHECK constraint should check
> > values only from curent row. From this point of view this thread is
> > about misusing the feature, not about bug.
>
> Well then... Time for the ability to create "database constraints"
> and not only "current row constraints".
>
> > * If somebody still thinks that it is a bug, he should clearly state
> > what the solution/correct behaviour should be.
> > So far I only read that CHECK should fire
> > - before operation, -after operation, -on commit,
> > but *none* of these options will solve the "problem".
> > (even if Firebird fires all of them)
> >
> > Think about this simple example:
> >
> > CREATE TABLE T (
> > I INTEGER CHECK (I = (SELECT COUNT(*) FROM T)) );
> >
> > INSERT INTO T VALUES (0);
> > COMMIT;
> > INSERT INTO T VALUES (1);
> >
> > The second insert will invalidate CHECK constraint for previously
> > inserted and committed value. How do you want to prevent this ?
> > Probably by always checking *whole* table for each invoked CHECK ???
> >
> > Does SQL standard really says that CHECK should be satisfied permanently,
> > and not only during the operation ?
>
> Deferred constraints should keep the database in a consistent
> state at the time of COMMIT. So yes.
>
> With regards,
>
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
> Server.
> Upscene Productions
> http://www.upscene.com
>
> ------------------------ Yahoo! Groups Sponsor --------------------~-->
> $9.95 domain names from Yahoo!. Register anything.
> http://us.click.yahoo.com/J8kdrA/y20IAA/yQLSAA/67folB/TM
> --------------------------------------------------------------------~->
>
> Yahoo! Groups Links
>
>
>

------- End of Original Message -------