5

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Darrel Miller
  • 139,164
  • 32
  • 194
  • 243
  • 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. – OMG Ponies Aug 25 '09 at 18:19

4 Answers4

3

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.

Eric Petroelje
  • 59,820
  • 9
  • 127
  • 177
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.

Adam Robinson
  • 182,639
  • 35
  • 285
  • 343
  • 1
    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. – Darrel Miller Aug 25 '09 at 19:01
  • 1
    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. – Adam Robinson Aug 25 '09 at 19:25
  • 1
    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 :-) – Darrel Miller Aug 25 '09 at 19:35
2

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.

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • 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(); – Darrel Miller Aug 25 '09 at 18:36
2

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

gbn
  • 422,506
  • 82
  • 585
  • 676