PostgreSQL 中更新具有非空和唯一约束的列的特殊情况

Question

这是一个说明 PostgreSQL 中实际问题的玩具示例。下面的示例使用 PostgreSQL 8.4.3 服务器，但我怀疑其他版本也有同样的问题。

给定下表：

=> create table tmp_foo (foo boolean not null unique, bar boolean not null unique);
=> insert into tmp_foo (foo, bar) values (true, true), (false, false);
=> select * from tmp_foo;
 foo | bar 
-----+-----
 t   | t
 f   | f

是否可以将该表修改为如下所示：

=> select * from tmp_foo;
 foo | bar 
-----+-----
 t   | f
 f   | t

而不删除行或修改表架构？这：

=> update tmp_foo set bar = not bar;
ERROR:  duplicate key value violates unique constraint "tmp_foo_bar_key"

不起作用。

如果允许删除，则：

=> create temp table tmp_foo_2 as select * from tmp_foo;
=> update tmp_foo_2 set bar = not bar;
=> delete from tmp_foo;
=> insert into tmp_foo select * from tmp_foo_2;

有效。对于此示例来说，它不是最简单的解决方案，但它可以轻松推广到更复杂的示例。

Answer 1

这样做需要可延迟的唯一约束。

我们在每列中拥有尽可能多的行唯一值。因此，要改变任何行，要么某些行必须暂时违反唯一约束，要么必须删除某些行以避免违反约束。可延迟的唯一约束让我们可以执行前者——临时违规（在事务内）。

如果您到目前为止，并且听起来是正确的，那么您问题的答案取决于 Postgres 版本。

仅 Postgres 8.4 允许推迟外键约束。推论是独特的约束不能被推迟。

Postgres 9.0 beta 理论上提供了可延迟的独特约束。我自己还没有尝试过，但这个功能已经推出很长时间了，所以我敢打赌，当他们决定实施它时，他们就做对了。

以下是关于 9.0 的两部分相关文档 唯一索引< /a> 和 设置约束< /a>.正如您在后一个链接中所看到的，在 9.0 文档中，通过 SET CONSTRAINTS 明确列出了支持延迟的唯一约束。我还没有探索这个新功能，我不能保证语义正是您所需要的。但这似乎只是事情。