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.

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.

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.

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.

Answer 4

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