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

Question

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

我正在创建fields_new具有相似结构（此处不重要）和一列的新表name。我需要一种方法来限制name要插入的值，使其fields_new不存在于中fields.name。

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

Answer 1

仅对新条目强制实施约束 fields_new

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

为了允许一些回旋余地（尤其是具有时间功能），STABLE可以容忍一些功能。显然，在具有并发写入访问权限的数据库中，这是不完全可靠的。如果引用表中的行发生更改，则它们可能违反了约束。

声明 约束的无效性质 NOT VALID （Postgres
9.1+）。这样，Postgres也不会尝试在还原过程中强制执行它（这可能注定会失败）。详细信息在这里：

• 恢复转储时禁用所有约束和表检查

该约束仅适用于新行。

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或PRIMARY KEY约束上fields_new(name)，以及对fields(name)。

双向执行

您可以更进一步，将上述CHECK约束反映在第二张桌子上。当两个事务同时写入两个表时，仍然不能保证避免恶劣的竞争条件。

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

只是，你需要触发器INSERT/ UPDATE/DELETE这两个表。