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

views:

137

answers:

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

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.

+1 A:

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 2009-08-25 18:17:44

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 2009-08-25 19:01:59

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 2009-08-25 19:25:35

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 2009-08-25 19:35:00

+1 A:

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 2009-08-25 18:21:08

+1: Indeed, sounds like a design issue.

John Sansom 2009-08-25 18:23:06

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 2009-08-25 18:36:15

+1 A:

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 2009-08-25 18:21:44

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

OMG Ponies 2009-08-25 18:25:22

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

Darrel Miller 2009-08-25 18:41:59

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

gbn 2009-08-25 18:29:00

ansaurus

tags:

views:

answers:

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

related questions