Updating database records with a unique constraint

Question

Given the following simple table structure (SQL Server 2008), I want to be able to maintain the uniqueness of a numerical sequence column, but I want to be able to update this value for any record (s).

CREATE TABLE MYLIST( ID int NOT NULL IDENTITY(1, 1) , TITLE varchar(50) NOT NULL , SEQUENCE int NOT NULL , CONSTRAINT pk_mylist_id PRIMARY KEY(ID) , CONSTRAINT uq_mylist_sequence UNIQUE(SEQUENCE) ); 

My interface allows me to iterate over the order of the elements and , I will know, before doing the update, which the non-overlapping sequence should be in , but how can I perform the update without encountering a violation of a single restriction?

For example, let's say I have these entries:

 ID TITLE SEQUENCE 1 APPLE 1 2 BANANA 2 3 CHERRY 3 

And I want to update their serial numbers to the following:

 ID TITLE SEQUENCE 1 APPLE 3 2 BANANA 1 3 CHERRY 2 

But in fact, I could deal with several dozen elements. Sequence numbers must not overlap. I was thinking of trying to use triggers or temporarily disable the restriction, but this seems to be creating more problems. I use C # and LINQ-to-SQL, but I am open to strictly database solutions.

Answer 1

You can assign them a negative value of their correct value, after which all updates will happen, make the last update in which you set SEQUENCE = -SEQUENCE .

This is not very effective, but since you say that you have only a few dozen items, I doubt that it will be noticeable. I also suggest that you can use negative numbers as “magic values” to indicate temporarily incorrectly assigned values.

Answer 2

The obvious way is to record in one batch. Internally, SQL defers constraint checks, so intermediate uniqueness doesn't matter.

Writing line by line does not make sense and causes a problem.

You can change this to write to the temporary table, and then “clear” the results at the end, even first checking for uniqueness over the temporary table.

 DECLARE @NewSeq TABLE (ID int, NewSeq int) INSERT @NewSeq (ID, NewSeq) VALUES (1, 3) INSERT @NewSeq (ID, NewSeq) VALUES (2, 1) INSERT @NewSeq (ID, NewSeq) VALUES (3, 2) UPDATE M SET SEQUENCE = NewSeq FROM MYLIST M JOIN @NewSeq N ON M.ID = N.ID 

Answer 3

If you really need to follow this insert workflow without knowing the correct order, and then to return with the update later to establish the correct order, I would say that your best option is to get rid of the unique constraint, because it causes you more problems than worth it. Of course, only you know how much this unique limitation is “worth” for your application.