Swapping two DB rows without violating constraints

Question

I have a table regionkey:

areaid  -- primary key, int
region  -- char(4)
locale  -- char(4)

The entire rest of the database is foreign-keyed to areaid. In this table there is an index on (region, locale) with a unique constraint.

The problem is that I have two records:

101   MICH   DETR
102   ILLI   CHIC

And I need to swap the (region,locale) fields between them, so that I wind up with:

101   ILLI   CHIC
102   MICH   DETR

The naive approach won't work because it violates the unique index on region and locale:

update regionkey
     set region='ILLI', locale='CHIC' where areaid = 101; -- FAILS
update regionkey
     set region='MICH', locale='DETR' where areaid = 102;

How can I do this? Is there an atomic way to make the swap? Suggestions?

Answer 1

BEst bet is to make three updates. Update the first record to a temp set of values, update the second record and then reupdate the first record to the values you want.

Answer 2

You can't defer constraint checks in SQL Server over multiple statements (unless you DISABLE) so you have to avoid the conflict or do it in one statement

update
    regionkey 
set
    region= CASE areaid WHEN 101 THEN 'ILLI' ELSE 'MICH' END, 
    locale= CASE areaid WHEN 101 THEN 'CHIC' ELSE 'DETR' END
where
    areaid IN (101, 102);

or, more conventionally (in a transaction this one)

update regionkey 
     set region='AAAA', locale='BBBB' where areaid = 101;
update regionkey 
     set region='MICH', locale='DETR' where areaid = 102;
update regionkey 
     set region='ILLI', locale='CHIC' where areaid = 101;

Edit: Why not swap keys not values? It usually achieves the sane result unless areaid has some meaning

update
    regionkey 
set
    areaid = 203 - areaid 
where
    areaid IN (101, 102);

Answer 3

Have you tried the simple act of wrapping it in a transaction?

I understand you can set up constraints to allow it to only enforce the constraint at the end of a transaction but I am unsure if your constraints are set up that way.

Answer 4

One suggestion, which may not be the safest for large record sets, would be to set both records to ' ' for both region & locale, and then execute two update statements, one for each record, like so:

UPDATE
    regionkey
SET
   region = '    ',
   locale = '    '
WHERE
    areaid in (101,102)

UPDATE
    regionkey
SET
    region = 'ILLI',
    locale = 'CHIC'
WHERE
    areaid = 101

UPDATE
    regionkey
SET
    region = 'MICH',
    locale = 'DETR'
WHERE
    areaid = 102

Like I said, this is probably not the safest way to go, but for a small data set it should be OK.

UPDATE: Larry correctly pointed out that the first UPDATE statement will violate the UNIQUE constraint. Use this instead for the first UPDATE:

UPDATE
    regionkey
SET
    region = areaid,
    locale = areaid
WHERE
    areaid in (101,102)

This way each intermediate region and locale is (or should be) unique.