Deferred constraints [CORE2445]

[firebird-automations]

Submitted by: Cosmin Apreutesei (cosmin_ap2)

Votes: 11

Implement commit-time constraint checking as per SQL specification.

It was on the 2006 roadmap for fb 3.0 but I can't find it on the tracker.

A few use cases: - allow foreign-key/not null-constrained back-references - switch two values from the same uniquely-constrained set with UPDATE instead of DELETE + INSERT ... add your own...

[firebird-automations]

Commented by: Philip Williams (unordained)

I don't know if the SQL spec says this, but in my idealistic opinion [commit-time] constraints ought to be able to see change from the current transaction + changes made by any other committed or limbo (partially committed two-phase) transactions. The built-in constraint types do this already -- primary, foreign, and unique constraints can reject changes even though you can't see the conflicts from within your transaction. But what about check constraints that do equivalent work, or that span more than one row? (Within-table constraints like "no two rows may have overlapping date ranges", or outside-table constraints like "all people must have at least two address") There are two forms of validity: "the changes made by this transaction make sense given their starting conditions" (constraint does not fail within the transaction view) and "the changes made by this transaction, when combined with other already-accepted changes, still make sense" (constraint does not fail within the view that would occur right after commit, when starting a new transaction). It's not *quite* like running the check constraint inside a read-only read-committed transaction (read-committed can't see limbo, right?) in addition to running it within the transaction itself, but close. This is to avoid situations where two transactions could cooperate to work around a constraint by each making changes that make sense by themselves, but that don't make sense once combined, yet ignoring each other by being concurrent (mvcc).

[firebird-automations]

Commented by: Cosmin Apreutesei (cosmin_ap2)

another use case:

you have two tables:

master_table (master_id, own_special_child_id, ...) child_table (child_id, master_id, ...)

you want to make a foreign key in master_table on (own_special_child_id, master_id) referencing child_table (child_id, master_id), that is, you want to constrain own_special_child_id to be in the set of children of master_id.

This way you could constrain the value in a required column to be part of a set that is defined by values of other columns in the same row. Putting own_special_child_id in a 3rd table is only a partial solution -- a trigger is still required on master_table on after insert to check for the presence of own_special_child_id in the 3rd table.

[firebird-automations]

Commented by: Andreas Prucha (ancpru)

I add my support for this request.

A customer of mine could be called a "constraint-freak". He really would like to have almost everything checked at DB-Level. Unfortunately this is not possible without deferred constraints, because some checks might spawn multiple tables.

[firebird-automations]

Commented by: @krilbe

I just had a similar issue with a unique constraint in the MDD framework ECO (http://www.capableobjects.com).

One workaround could be to drop the constraint before the updates and recreate it right before commit, or something like that. Not really nice, but should work.

For my particular case I think I'm able to separate the update into two transactions without taking a significant risk. :-)

[firebird-automations]

Commented by: tonim (tonim)

Without deferred constraints, replication (record version implementation) is not possible. It's very usual that tables have intererelated constraints. So is not possible to determine the update order to sinchronize the databases. My current workaround is to replace some constraints with triggers.