具有独特约束的原子多行更新

坚爷

我有一个按照标签顺序显示的标签列表。为了确保没有两行可以有相同的等级，它们的值是唯一的：
create table label (  id_label serial not null, rank integer not null, title text not null, constraint pri primary key (id_label), constraint unq unique (rank))不管是PostgreSQL还是MySQL，他们都表现出同样的行为。查询可能看起来像select title from label order byrank。假设该表包含：
id_label rank title1      10   10     Cow2    20   20    20       20               20  Apple3        455        Horse4          60                  Beer现在，假设我想重新排序两个标签，例如Apple在Cow以前的排名。最简单的方法是交换等级：
update labelset rank = case when rank = 20 then 10 else 20 endwhere id_label in (1,2)没有。也没有:
update labelset rank = case when rank = 20 then rank - 10 else rank   10 endwhere id_label in (1,2)甚至没有：
; W. M5 I2 N/ Y' u% V6 \5 Aupdate labelset rank = 30 - rankwhere id_label in (1,2)每次，在第一行更新时，都会触发唯一的约束，并暂停操作。如果我能把支票推迟到账单的最后，那就可以了。PostgreSQL和MySQL上都发生。
使用ACID安全解决方案如下：
7 h$ i! M( |. i9 s[ol]开始交易
选择表中的第一、第二条记录和最高(最大)等级(副手可能需要并集)
将第一条记录更新为等级=最大值  1
将第二条记录更新为第一名
将第一条记录更新为第二名
! @6 Y6 @5 Q% x& g) U. ^犯罪[/ol]这真的很丑。更糟糕的是删除约束，更新，然后重新创建约束。授予这些特权会给操作人员带来麻烦。所以我的问题是：是否有一种我忽略的简单技术来解决这个问题，还是我是SOL？
                                                               
& ]2 x+ [, f% o. U' A5 k3 L; ^    解决方案:                                                               
1 Q: G$ ?0 H* L                                                                在PostgreSQL这只能通过9.0版以好的方式解决，因为你可以在那里定义唯一的约束来延迟。
使用PostgreSQL 9.您只需执行以下操作：
create table label (  id_label serial not null, rank integer not null, title text not null, constraint pri primary key (id_label));alter table label add constraint unique_rank unique (rank)       deferrable initially immediate;然后更新就这么简单:
begin;set constraints unique_rank DEFERRED;update rank   set rank = case when rank = 20 then 10 else 20 end   where id_label in (1,2);commit;编辑：
如果你不想麻烦地将约束设置为事务延迟，您只需将约束定义为initially deferred。