Deferred constraint checking?

Another discussion we’ve had on the local mailing lists lately has been around whether or not the ability to defer constraint checking (at least foreign key constraints) until the end of a transaction would be useful in SQL Server.


I see a lot of convoluted update code from things like ADO.NET datasets that would be greatly simplified if you could say:


a) start a transaction


b) send all the updates in any order


c) commit the transaction (and check the RI constraints at this point)


I’d love to hear your thoughts. Oracle has had it for a long time. Would you find it useful? If so, vote for it here: http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=8830bd75-ec68-4e8f-a4e5-3ab293a979db

2 thoughts on “Deferred constraint checking?”

  1. I see at least two potential benefits to deferred constraint checking:

    1. Avoiding the significant time required to reorder transaction sets in the middle tier.

    2. SQL Server could consistently order the constraint checking and resultant locking to avoid deadlocks. (Right now we are obtaining deadlocks when transactions deleting rows are inter-mixed with transactions inserting/updating rows on the same tables…)

    My vote was 5 (important).

    Best regards,

    Richard

  2. VERY VERY VERY important !!!

    We have a program that synchronize the data between server and local databases. Without this feature, it is tooooo difficult to figure out the order of the update records, which causes unique key constraint violation error!!!

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>