Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Deferred constraints [CORE2445] #2859

Open
firebird-automations opened this issue Apr 30, 2009 · 5 comments
Open

Deferred constraints [CORE2445] #2859

firebird-automations opened this issue Apr 30, 2009 · 5 comments

Comments

@firebird-automations
Copy link
Collaborator

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant