Unique key constraint error when updating a SQL Server table in ASP.NET application

Question

I have a simple table with the following:

ID  MarketID Lead
1   1        true
2   1        false
3   2        true
4   2        false

It is a table of paired values, so the pattern is that there are always two rows with the same MarketID, but one must be false (0) and one must be true (1). This is enforced by a unique key constraint (which ensures MarketID and Lead combined are unique) that is working as expected.

However, I want a user to be able to change the "Lead" column, and switch the values.

I have this working fine up until I call SubmitChanges on:

table.AttachAll(updated);
table.Context.Refresh(RefreshMode.KeepCurrentValues, updated);
table.Context.SubmitChanges();

Where updated is the two changed rows

A quick look at SQL Profiler shows SQL Server (2008) is updating row by row, so it is first updating the true value to false, but before it gets to change the false value to true on the second row it causes a break and fails, because now both are set to false.

Does anybody know a way around this?

Answer 1

1. Temporarily disable & re-enable the UNIQUE constraint.

2. Temporarily allow IDENTITY INSERTS, then delete and re-create both rows.

3. Temporarily allow IDENTITY INSERTS, then update the ID value of both rows. (this might be a 3-step process if the ID column has a UNIQUE constraint)

4. Allow NULLs in the Lead column, and use NULL to temporarily bypass the UNIQUE constraint while you switch one row and then the other.

...and if I think of any others I'll add them later.