问题描述
在我的PostgreSQL 9.4 数据库中，我有一个表 fields 和一列 name
In my PostgreSQL 9.4 database, I have a table fields with a column name with unique values.
我正在创建具有相似结构的新表 fields_new （此处不重要）以及名称列。我需要一种方法来约束 name 值，以将其插入到 fields_new 中，使其不会出现在 fields.name 。
I'm creating a new table fields_new with a similar structure (not important here) and a column name as well. I need a way to constraint name values to be inserted to the fields_new not to be present in fields.name.
例如，如果 fields.name 包含值'颜色'和'length'，我需要防止 fields_new.name 包含颜色 或'length'值。因此，换句话说，我需要提供两个表中的 name 列之间没有任何重复的值。约束应该是双向的。
For example, if fields.name contains the values 'color' and 'length', I need to prevent fields_new.name from containing 'color' or 'length' values. So, in other words I need to provide that the name columns in both tables do not have any duplicate values between them. And the constraint should go both ways.
推荐答案
仅对 fields_new
CHECK 约束应该是不可变的，通常
允许一些回旋余地（尤其是具有时间功能的表） STABLE
To allow some leeway (especially with temporal functions) STABLE functions are tolerated. Obviously, this cannot be completely reliable in a database with concurrent write access. If rows in the referenced table change, they may be in violation of the constraint.
code>函数是可以容忍的。显然，在具有并发写入访问权限的数据库中，这不能完全可靠。如果引用表中的行发生更改，则它们可能违反了约束。
Declare the invalid nature of your constraint by making it NOT VALID (Postgres 9.1+). This way Postgres also won't try to enforce it during a restore (which might be bound to fail). Details here:
声明，将约束设为 （Postgres 9.1+）。这样，Postgres也不会在还原过程中尝试执行它（这可能会失败）。此处的详细信息：
- Disable all constraints and table checks while restoring a dump
The constraint is only enforced for new rows.
该约束仅适用于新行。
Plus, of course, a UNIQUE or PRIMARY KEY constraint on fields_new(name) as well as on fields(name).当然，还有 UNIQUE 或 fields_new（name）和 fields（name）上的。 PRIMARY KEY 约束
Related:
相关：
You could go one step further and mirror the above CHECK constraint on the 2nd table. Still no guarantees against nasty race conditions when two transactions write to both tables at the same time.
您可以再走一步，并在第二个镜像上面的 CHECK 约束表。当两个事务同时写入两个表时，仍然不能保证避免恶劣的竞争条件。
Or you could maintain a "materialized view" manually with triggers: a union of both name columns. Add a UNIQUE constraint there. Not as rock solid as the same constraint on a single table: there might be race conditions for writes to both tables at the same time. But the worst that can happen is a deadlock forcing transactions to be rolled back. No permanent violation can creep in if all write operations are cascaded to the "materialized view".
或者您可以维护物化视图手动使用触发器：两个 name 列的并集。在此处添加 UNIQUE 约束。不像对单个表的约束那样坚如磐石：可能存在同时写入两个表的竞争条件。但是可能发生的最坏情况是僵局，迫使事务被回滚。如果所有写操作都级联到物化视图，则不会出现永久违规。
Similar to the "dark side" in this related answer:
类似于此相关答案中的暗面：
- Can PostgreSQL have a uniqueness constraint on array elements?
Just that you need triggers for INSERT / UPDATE / DELETE on both tables.
这篇关于SQL约束，用于检查另一个表中是否不存在值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！