Subject Re: [firebird-support] Re: extract constraint from Domain
Author Ann W. Harrison
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.