Is it possible to defer referential integrity checks until the end of a transaction in SQL Server?

Question

I recently read in Fowler's PofEA that some database engines allow you to defer RI checks until the end of the transaction. Is this possible in SQL Server?

The problem comes with some scenarios where I am trying to save changes that include insertions and deletions, it can be tricky to determine the correct order in which to perform the operations to avoid RI failures. I know that at the end of the transaction the RI will be good so it would seem ideal to defer these checks.

Jewess

You could make it happen by disabling/enabling constraints at the start & end of a sproc, but that's so scary a route that I'd really question the application and/or data model. – Putscher

Answer 1

Looks like sql server doesn't allow this, but you aren't the only one who wants it. It's part of the SQL 92 standard, so they've had 17 years now to figure it out. Then again, MySQL doesn't support it either (although Oracle does).

Reordering the statements is probably your best option.

Selective

Ugh. There's far more useful things they should implement than that. Packages, actual syntax for hierarchical queries... – Putscher

Thanks for the link. Glad to know I'm not the only one who thinks this would be useful. – Jewess

Answer 2

You would be better off determining the correct order so that the transaction can fail at the appropriate time. This should be a one-time determination.

Connivent

The point is that the transaction should not fail. The RI check that is failing will be irrelevant by the end of the transaction because the related rows will be deleted. It is also a waste of time to do the check. – Jewess

It should not, but it can. While this feature may be present in other RDBMS's, it seems very "smelly" to me. It shouldn't be that difficult to determine the proper order of execution. – Connivent

Well, it may seem smelly to you, but Martin Fowler thinks it is useful and the ANSI SQL-92 standard says that is how it should work, so that's good enough for me :-) – Jewess

Answer 3

SQL Server does not have this feature.

May I humbly suggest that if it's tricky to determine the correct insertion order (or there is not one), that your database design may need to be refactored or at least examined very carefully.

Forearm

It is not that I don't know the order of inserts but I am using a basic ORM that will either INSERT or DELETE based on the state of the objects. If I have code that says A.Save(); B.Save(); C.Save(); and those saves do INSERTs then all is good. However, if those objects have been deleted then I really need to do C.Save(); B.Save(); A.Save(); – Jewess

Answer 4

If you are on SQL Server 2008, you can use MERGE to combine insert/delete into a single RI-safe transaction...

Caftan