具有唯一约束的原子多行更新

Question

我有一张标签列表，按标签顺序显示。为了确保没有两行可以具有相同的等级，它们的值是唯一的：

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 by rank。假设该表包含：

id_label rank title
1        10   Cow
2        20   Apple
3        45   Horse
4        60   Beer

现在，假设我想重新排序两个标签，例如Apple在Cow之前排名。最简单的方法是交换其等级值：

update label
set rank = case when rank = 20 then 10 else 20 end
where id_label in (1,2)

没有。也不：

update label
set rank = case when rank = 20 then rank - 10 else rank + 10 end
where id_label in (1,2)

甚至没有：

update label
set rank = 30 - rank
where id_label in (1,2)

每次，唯一约束都会在第一行更新时触发，并中止该操作。如果我可以将支票推迟到帐单末尾，那将是可以的。这在PostgreSQL和MySQL上都发生。

使用ACID安全的解决方法是：

1. 开始交易
2. 选择表中第一，第二条记录和最高（最大）等级（副手可能需要并集）
3. 将第一条记录更新为等级=最大值+ 1
4. 将第二条记录更新为第一名
5. 将第一条记录更新为第二名
6. 犯罪

那真是丑陋。更糟糕的是删除约束，更新然后重新创建约束。将此类特权授予操作角色会带来麻烦。所以我的问题是：是否有一种我忽略了的简单技术可以解决此问题，或者我是SOL？

Answer 1

在PostgreSQL中，这只能通过9.0版以“不错”的方式解决，因为您可以定义唯一的约束以在那里可以延期。

使用PostgreSQL 9.0，您只需执行以下操作：

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。