交换两个数据库行而不违反约束 [英] Swapping two DB rows without violating constraints

问题描述

我有一张桌子regionkey:

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

整个数据库的其余部分都用外键键入areaid.在此表中，有一个具有唯一约束的(区域，区域设置)索引.

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.

问题是我有两条记录:

101   MICH   DETR
102   ILLI   CHIC

我需要在它们之间交换(region，locale)字段，这样我才能得出结论:

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?

推荐答案

您不能在SQL Server中推迟对多个语句的约束检查(除非您禁用)，所以您必须避免冲突或在一个语句中执行

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;

为什么不交换键而不交换值?除非Areaid具有某些含义，否则通常可以达到理智的结果

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); 

这篇关于交换两个数据库行而不违反约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！