Is there a reason that CHECK constraints run in the user context?

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Ann W. Harrison
Sent: Tuesday, January 03, 2006 2:21 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Re: extract constraint from Domain

Rick Debay wrote:

> Personally I'd prefer the foreign key, but my doppelganger may be
> having issues with them due to FB 1.5 issues.
> And IMNSHO the check constraint should be TX safe the same as a FK,
> having the same violations.

Not true, unfortunately. The foreign key is evaluate in the system
transaction context. Triggers are evaluated in the user transaction
context. A foreign key constraint will fail if two concurrent
transactions make changes that invalidate the constraint.


Consider the case that occurs when one transaction attempts to delete a
record from the referenced table that has no matching committed records
in the referencing table while a concurrent transaction stores a record
in the referencing table that matches the record being deleted.
A foreign key constraint will detect and stop the inconsistent result.
Triggers on the two tables will not.

Regards,


Ann



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item on
the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links