locked
How to handle simple uniqueness constraints RRS feed

  • Question

  • I have a simple table in my database like this...

    CREATE TABLE [dbo].[Tag]
    (
    	[TagId] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, 
    	[Title] NVARCHAR(50) NOT NULL, 
    )
    
    CREATE UNIQUE INDEX [IX_Tag_Title] ON [dbo].[Tag] ([Title])

    I used the automated wizards to create a data model and expose this table via oData. I then created an oData client that downloads the table contents, swaps the Title fields of the first two tags, and then submits changes. The update fails because the uniqueness constraint is violated. I think (but am not sure) that changes are submitted one row at a time, and database constraints are checked after each row update rather than after all changes are submitted. Is there some simple way around this?

    If that doesn't make sense, here is a concrete example. Suppose there are two tags in the database as shown below. If you download these tags via oData and swap the titles then the first tag will have the name "Work" and the second will have the tag "Home". When you submit these changes to the database I think it is done one row at a time. If that first row being submitted is the row with ID A8F030F5 then the database will complain that the title index is violated because there are two rows with the title "Work".

    A8F030F5... "Home"

    FC6239ED... "Work"

    NOTE: I just found a thread with almost an identical issue back in 2008. I'm wondering if this has been fixed since then. It seems to me that without deferring constraint checking until the end of a transaction or until the end of some form of "multi-update/delete/insert" statement that SQL Server has almost no ability to enforce business rules other than super simple row-level check constraints. Writing stored procedures to handle this kind of thing is a kludge. I should be able to define constraints that span tables - such as an arbitrarily complex SELECT statement that must return 0 rows - and SQL Server should enforce it. 

    http://social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/thread/76d3ade4-38a3-4205-8c98-260e6ce7b217

    • Edited by JustinMag Saturday, July 21, 2012 7:03 AM Found related thread
    Saturday, July 21, 2012 6:54 AM

All replies

  • Hi,

    I assume you used the WCF DS client library (DataServiceContext and friends) to perform the update. And that you call DataServiceContext.SaveChanges() (no parameters) in your code. If that's the case, then this is expected since the client sends the two updates as two separate requests to the server. And thus the server performs two separate transactions and fails (as it should).

    To fix this you can call SaveChanges(SaveChangesOption.Batch) in your client code. This will send the two updates packaged in a single request to the server. Depending on the server setup this should end up executing a single transaction and thus should work for you.

    Thanks,


    Vitek Karas [MSFT]

    Saturday, July 21, 2012 8:14 AM
    Moderator
  • I think I was already doing that. In my case I'm using a Windows Phone client. The code to save the changes is shown here...

    _context.BeginSaveChanges(SaveChangesOptions.Batch, OnSaveChangesCompleted, _context);
    

    I then tried to accomplish the same thing using Sql Server Management Studio and the query editor. My query is this...

    BEGIN TRAN
    UPDATE    Tag SET Title = N'B' WHERE (TagId =  N'a8f030f5-caf3-43ef-a072-0406e548b9d4')
    UPDATE    Tag SET Title = N'A' WHERE (TagId =  N'9863D595-4C63-4886-A8E2-0BF737B5DC2C')
    COMMIT TRAN

    If I try to swap the values of the two tags I get an error indicating that the uniqueness index on Title is violated. I'm not a T-SQL expert so maybe I'm doing something wrong here. 

    Saturday, July 21, 2012 4:46 PM
  • I don't think there is anything wrong with your syntax. The SQL Server is executing the two statements and evaluating the unique constraint at each step, within the context of a transaction. The simplest solution here would be to use variables to swap the two values, changing one line to a temporary value so that the two Titles are never the same:

    DECLARE @temp1 NVARCHAR(50)
    DECLARE @temp2 NVARCHAR(50)
    
    BEGIN TRAN
    SELECT @temp1=title FROM Tag WHERE (TagId =  N'a8f030f5-caf3-43ef-a072-0406e548b9d4')
    SELECT @temp2=title FROM Tag WHERE (TagId =  N'9863D595-4C63-4886-A8E2-0BF737B5DC2C')
    UPDATE    Tag SET Title = N'transition-value' WHERE (TagId =  N'a8f030f5-caf3-43ef-a072-0406e548b9d4')
    UPDATE    Tag SET Title = @temp1 WHERE (TagId =  N'9863D595-4C63-4886-A8E2-0BF737B5DC2C')
    UPDATE    Tag SET Title = @temp2 WHERE (TagId =  N'a8f030f5-caf3-43ef-a072-0406e548b9d4')
    COMMIT TRAN

    The alternative that I see would be to make changes on the application side to prevent the need for uniqueness or the need to swap values on this table. I doubt there is any way to prevent the SQL Server from evaluating the unique constraint until the end of the transaction; that would seem like a very core change to the engine's operation that would affect other areas such as locking.

    Please let us know if you have further questions on this matter.

    Thursday, August 2, 2012 8:45 PM