Updating database records with unique constraint

Question

Given the following simple table structure (SQL Server 2008), I want to be able to maintain uniqueness of my numerical sequence column, but I want to be able to update that value for any given 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 jumble up the order of the items and I will know prior to doing the update which non-overlapping sequence they should all be in, but how do I perform the update without being confronted with a violation of the unique constraint?

For instance, say I have these records:

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

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

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

But really I could be dealing with a couple dozen items. The sequence numbers must not overlap. I've thought about trying to use triggers or temporarily disabling the constraint, but that would seem to create more issues. I am using C# and LINQ-to-SQL, but am open to strictly database solutions.

Answer 1

The obvious way is to write as one batch. Internally, SQL will defer the constraint checks so intermediate uniqueness is irrelevant.

Writing row by row does not make sense and causes the problem you have.

You can change this to write into a temp table, and then "flush" the results at the end, even check uniqueness over the temp table first.

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 2

You could assign them the negative of their correct value, then after all updates have occurred, do a final update where you set SEQUENCE = -SEQUENCE.

It is not very efficient, but since you say you only have a couple dozen items, I doubt the impact would be noticeable. I am also assuming that you can use negative numbers as "magic values" to indicate temporarily mis-assigned values.