用于检查值是否不存在于另一个表中的 SQL 约束

Question

在我的 PostgreSQL 9.4 数据库中，我有一个表 fields，其中的列 name 具有唯一值。

我正在创建一个新表 fields_new，它具有类似的结构(此处不重要)和一个列 name。我需要一种方法来限制要插入 fields_new 的 name 值不出现在 fields.name 中。

例如，如果 fields.name 包含值 'color' 和 'length'，我需要防止 fields_new .name 来自包含 'color' 或 'length' 值。因此，换句话说，我需要确保两个表中的 name 列之间没有任何重复值。并且约束应该是双向的。

Answer 1

仅对 fields_new 中的新条目实现约束

CHECK 约束应该是不可变的，这通常会排除对其他表的任何类型的引用，这些表本质上不是不可变的。

为了允许一些回旋余地(特别是对于时间函数)STABLE 函数是可以容忍的。显然，这在具有并发写访问的数据库中不能完全可靠。如果引用表中的行发生更改，则它们可能违反约束。

通过 NOT VALID 声明您的约束的无效性质(Postgres 9.1+)。这样 Postgres 也不会在恢复期间尝试强制执行它(这可能会失败)。详情在这里:

• Disable all constraints and table checks while restoring a dump

约束只对新行强制执行。

CREATE OR REPLACE FUNCTION f_fields_name_free(_name text)
  RETURNS bool AS
$func$
SELECT NOT EXISTS (SELECT 1 FROM fields WHERE name = $1);
$func$  LANGUAGE sql STABLE;

ALTER TABLE fields_new ADD CONSTRAINT fields_new_name_not_in_fields
CHECK (f_fields_name_free(name)) NOT VALID;

此外，当然还有 fields_new(name) 和 fields(name )。

相关:

• CONSTRAINT to check values from a remotely related table (via join etc.)
• Function to update a status flag for validity of other column?
• Trigger vs. check constraint

双向执行

您可以更进一步，在第二个表上镜像上述 CHECK 约束。当两个事务同时写入两个表时，仍然无法保证不会出现令人讨厌的竞争条件。

或者您可以使用触发器手动维护“物化 View ”:两个name 列的并集。在那里添加一个 UNIQUE 约束。不像单个表上的相同约束那样坚如磐石:可能存在同时写入两个表的竞争条件。但可能发生的最坏情况是死锁迫使交易回滚。如果所有写操作都级联到“实体化 View ”，就不会出现永久性违规。

类似于此相关答案中的“阴暗面”:

• Can PostgreSQL have a uniqueness constraint on array elements?

只是您需要在两个表上执行 INSERT/UPDATE/DELETE 的触发器。