Option to defer foreign key constraint checking until transaction commit
There are various "chicken and egg" scenarios where it would be desirable to defer the checking of referential integrity constraints until commit time on a transaction.
Upvotes: 106<=-=Apr 20 2006 7:33PM=-=>
As a further example, consider flushing the contents of an ADO.NET dataset to the database server. Currently, the order of the updates is critical. It would simplify code design if you could just a) start a transaction b) send all the updates c) commit them.<=-=Oct 13 2006 1:35PM=-=>
Thanks for the feedback. We are aware of this and looking into it for a future release.
This is a duplicate of feedback id 124624.
The SQL ANSI-92 standard is to defer constraint checking until the commit phase – only 16 years ago now.
It is not a question of performance or faster dev, it is a fundamental to any database to be able to change a record from one consistent state to another consistent state within a transaction, without constraints stopping your halfway – because SQL Server is checking the constraints too early.
This is a particular problem with self-referential tables – and there is no neat workaround.
Is there any new word on adding this to a future release? I agree with Mike Casey’s statements here. Fowler also mentions this as a requirement of the DB for the Unit of Work pattern (PoEAA) if you don’t want to implement your own topological sort (to sort inserts, updates, and deletes per FK constraints, before issuing to the DB). To quote, “Most databases allow this, and if available there’s no good reason not to do it.”
I am also curious about the FK no check/revert code sample under the related feedback id 124624. Is MS recommending this? “This could work for you in some situations,” does not sound like a strong recommendation. I am concerned about the performance aspects of this approach, especially if I make the query/batch dynamically discover the FKs it must manipulate on every multi-table batch (I would like to do this to avoid tracking every FK in the DB, with every table each applies to, somewhere in the DAO layer – that could be done, but it seems like a maintenance nightmare). If someone on the MSFT side could comment on these concerns, I’d really appreciate it.
Thanks!<=-=Jan 13 2011 8:18AM=-=>
I would just like to add that this applies not only to foreign key constraints, but also to unique indexes and check constraints. (especially check constraints dealing with multiple tables)<=-=Feb 11 2011 3:15PM=-=>
If this is implemented, it would be ideal if after every statement in a transaction the optimizer knew whether any given constraint is currently valid in that transaction. So don’t just push the evaluation to commit, keep a running track. Otherwise intermediate queries during long transactions would suffer from serious performance issues due to inability to optimize based on constraint assumptions.<=-=Jan 23 2013 3:13AM=-=>
There is single feature that alone could make me change to Oracle: deferred constraints. Oracle has it, SQL Server does not.
The problem is that there are temporary (or transient) situations where the unique constraint is not satisfied. Imagine that we have two objects with a ‘Name’ property and this name is defined as unique in the database. If we now need to swap the names (i.e Name1 → Name2 and Name2 → Name1) then the end result is perfectly OK, we have unique names. But after the first update (Name1 → Name2) SQL Server decides that we now have two objects named ‘Name2’ and we get a nasty exception thrown in our face.
There are workarounds for this but these involve lots of effort, ruin the design of the data access layer and kills performance.<=-=Nov 26 2014 12:35PM=-=>
I need this.<=-=Jan 5 2016 7:04AM=-=>
I’d just like to echo the general sentiment of others hear. This has been in the ANSI standard since SQL92, and its absence in SQL Server represents a major development headache. EF, NHibernate, Dapper.SimpleSave, etc., do mitigate it but there are situations in which these cannot be used (and, on the OSS front, it’s causing me a headache with SimpleSave at the moment).
At the start of a transaction the database will be in a consistent state. When the transaction is committed, it should also be in a consistent state, and that’s the important point. During the transaction uncommitted changes may transiently contain inconsistencies, but checks should not be enforced until commit. SQL Server simply checks constraints too early, as MikeCasey said, and it is not OK. What it does is add complexity and overhead to development, leads to weird and hard to fix bugs, and there are just too many chicken and egg scenarios within many complex production schemas where you end up having to relax constraints in order to make INSERTs, UPDATEs, and DELETEs work, which feels like fundamentally the wrong solution. Honestly, it’s these kinds of issues that make people look to NoSQL for a solution. I’m not saying it’s always the right solution, but I can totally understand the frustration.
The use of CASCADE can appear to simplify matters but CASCADE feels risky and used unwarily can lead to unwanted loss of data, especially in a complex schema.
Again, as others have said, Oracle supports this, and so does PostgreSQL, which significantly eases developing with these databases. I notice this still isn’t supported in SQL Server 2016, but SQL Server really needs to catch up here. You could literally do nothing else for SQL Server 2018 but support this and it would be worth the upgrade. Because here’s the thing: honestly, most of the functionality added to SQL Server post 2008 goes unused by many of my clients at least (online index maintenance springs to mind as an exception), whereas deferred constraint checks would help all of them.<=-=Jan 5 2016 7:07AM=-=>
I’ve no idea if this is accurate or not but, interestingly, I just ran across this post on Stackoverflow where the answer suggests that in SQL Server 6.5 and earlier, deferred constraint checking was supported: http://stackoverflow.com/questions/5974731/does-sql-server-allow-constraint-violations-in-a-transaction-as-long-as-its-not. Like I say, don’t know if it’s accurate, but if it’s true I’m pretty shocked Microsoft chose to remove support.<=-=May 29 2016 8:14AM=-=>
We need this as well!<=-=Jun 30 2017 7:25AM=-=>
Just to add a concrete example of the limitations imposed by not having deferrable constraints, say that you have Department and Employee tables. Each Department should have exactly one associated contact Employee, and each Employee should have exactly one Department. The natural way to enforce this relationship would be to have Department.ContactEmployeeId and Employee.DepartmentId columns that are NON NULL and have foreign keys pointing to the primary key of the other table. Without deferrable constraints, creating or deleting departments could be problematic if the contact employee wasn’t/won’t be associated with any other department.
Matthias Kurz commented
Any plans so far to implement this?