Postgres何时检查唯一约束？ [英] When does Postgres check unique constraints?

问题描述

我有一个列 sort_order ，上面有唯一的约束。
在Postgres 9.5上，以下SQL失败：

I have a column sort_order with a unique constraint on it. The following SQL fails on Postgres 9.5:

UPDATE test
SET sort_order = sort_order + 1;

-- [23505] ERROR: duplicate key value violates unique constraint "test_sort_order_key"
--   Detail: Key (sort_order)=(2) already exists.

很明显，如果 sort_order 的值是唯一的在更新之前，它们在更新之后仍然是唯一的。为什么是这样？

Clearly, if the sort_order values were unique before the update, they will still be unique after the update. Why is this?

相同的语句在Oracle和MS SQL上工作正常，但在MySQL和SQLite上也失败。

以下是SQL小提琴的完整设置代码：

Here's the complete setup code for a SQL fiddle:

DROP TABLE IF EXISTS test;
CREATE TABLE test (
  val        TEXT,
  sort_order INTEGER NOT NULL UNIQUE
);

INSERT INTO test
VALUES ('A', 1), ('B', 2);

推荐答案

Postgres决定检查<$ c $类型的约束c>立即与SQL标准中建议的时间不同。

Postgres decides to check constraints of type IMMEDIATELY at a different time than proposed in the SQL standard.

具体来说， 设置约束 状态（强调我的意思） ：

Specifically, the documentation for SET CONSTRAINTS states (emphasis mine):

在插入或修改行时始终立即检查NULL和CHECK约束（不在语句末尾） ）。尚未声明为DEFERRABLE的唯一性和排除约束也会被立即检查。

NOT NULL and CHECK constraints are always checked immediately when a row is inserted or modified (not at the end of the statement). Uniqueness and exclusion constraints that have not been declared DEFERRABLE are also checked immediately.

Postgres选择使用产生结果的计划执行此查询 sort_order 和 IMMEDIATELY 的临时冲突失败。请注意，这意味着对于相同的架构和相同的数据，相同的查询可能会执行或失败，具体取决于执行计划。

Postgres chooses to execute this query using a plan that results in a temporary collision for sort_order and IMMEDIATELY fails. Note that means that for the same schema and the same data, the same query may work or fail depending on the execution plan.

您必须将约束设为 DEFERRABLE 或 DEFERRABLE INITIALIAL DEFERRED ，这将约束的验证延迟到交易结束或直到执行语句 SET CONSTRAINTS ...立即的地步。

You'll have to make the constraint DEFERRABLE or DEFERRABLE INITIALLY DEFERRED, which delays verification of the constraint until the end of the transaction or up to the point where a statement SET CONSTRAINTS ... IMMEDIATE is executed.

这篇关于Postgres何时检查唯一约束？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！