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

在我的 PostgreSQL 中9.4数据库，我有一个表fields有一个柱子name具有独特的价值观。

我正在创建一个新表fields_new具有类似的结构（这里不重要）和一个列name以及。我需要一种方法来约束name要插入的值fields_new不存在于fields.name.

例如，如果fields.name包含值'color' and 'length'，我需要阻止fields_new.name来自包含'color' or 'length'价值观。所以，换句话说，我需要提供name两个表中的列之间没有任何重复值。而且约束应该是双向的。

仅对新条目强制执行约束fields_new

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

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

Declare通过使您的约束无效NOT VALID http://www.postgresql.org/docs/current/interactive/sql-altertable.html（Postgres 9.1+）。这样，Postgres 也不会在恢复期间尝试强制执行它（这可能必然会失败）。详细信息在这里：

• 恢复转储时禁用所有约束和表检查 https://dba.stackexchange.com/a/75635/3684

仅对新行强制执行该约束。

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;

当然，再加上一个UNIQUE or PRIMARY KEY约束于fields_new(name)以及关于fields(name).

Related:

• CONSTRAINT 检查远程相关表中的值（通过连接等） https://stackoverflow.com/questions/27107034/constraint-to-check-values-from-a-remotely-related-table-via-join-etc/27107470#27107470
• 更新其他列有效性状态标志的函数？ https://stackoverflow.com/questions/31588480/function-to-update-a-status-flag-for-validity-of-other-column/31592427#31592427
• 触发器与检查约束 https://stackoverflow.com/questions/18409952/trigger-vs-check-constraint/18411888#18411888

双向执行

您可以更进一步并反映上述内容CHECK第二个表的约束。当两个事务同时写入两个表时，仍然不能保证避免出现令人讨厌的竞争条件。

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

类似于此相关答案中的“黑暗面”：

• PostgreSQL 可以对数组元素有唯一性约束吗？ https://stackoverflow.com/questions/8016776/can-postgresql-have-a-uniqueness-constraint-on-array-elements/8017013#8017013

只是你需要触发器INSERT / UPDATE / DELETE在两张桌子上。