sql - Updating database records with unique constraint -

given following simple table structure (sql server 2008), want able maintain uniqueness of numerical sequence column, want able update value 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 jumble order of items , i know prior doing update non-overlapping sequence should in, how perform update without being confronted violation of unique constraint?

for instance, have these records:

id  title   sequence 1   apple   1 2   banana  2 3   cherry  3 

and want update sequence numbers following:

id  title   sequence 1   apple   3 2   banana  1 3   cherry  2 

but dealing couple dozen items. sequence numbers must not overlap. i've thought trying use triggers or temporarily disabling constraint, seem create more issues. using c# , linq-to-sql, open strictly database solutions.

you assign them negative of correct value, after updates have occurred, final update set sequence = -sequence.

it not efficient, since have couple dozen items, doubt impact noticeable. assuming can use negative numbers "magic values" indicate temporarily mis-assigned values.