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

In my PostgreSQL 9.4 database, I have a table fields with a column name with unique values.

在我的PostgreSQL 9.4数据库中，有一个具有惟一值的列名的表字段。

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_new(这里不重要)和列名。我需要一种方法来将名称值插入到fields_new中，而不是在字段中显示。

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.name包含值'color'和'length'，我需要防止fields_new.name包含'color'或'length'值。因此，换句话说，我需要提供两个表中的name列之间没有任何重复的值。约束应该是双向的。

2 个解决方案

#1

Only enforce constraint for new entries in fields_new

CHECK constraints are supposed to be immutable, which generally rules out any kind of reference to other tables, which are not immutable by nature.

检查约束应该是不可变的，这通常排除了对其他表的任何引用，而这些表本身并不是不可变的。

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.

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

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:

通过使约束无效来声明约束的无效性质(postgres9.1 +)。这样，Postgres也不会尝试在恢复期间执行它(这可能会失败)。细节:

• Disable all constraints and table checks while restoring a dump
• 在恢复转储时禁用所有约束和表检查。

The constraint is only enforced for new rows.

约束只对新行执行。

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;

Plus, of course, a UNIQUE or PRIMARY KEY constraint on fields_new(name) as well as on fields(name).

当然，外加fields_new(name)和字段(name)上的惟一或主键约束。

Related:

相关:

• CONSTRAINT to check values from a remotely related table (via join etc.)
• 从远程相关的表(通过join等)检查值的约束。
• Function to update a status flag for validity of other column?
• 函数更新其他列的有效性状态标志?
• Trigger vs. check constraint
• 触发器和检查约束

Enforce both ways

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.

您可以更进一步，在第二个表上镜像上面的检查约束。当两个事务同时写入两个表时，仍然不能保证不会出现严重的竞争状况。

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".

或者您可以使用触发器手工维护“物化视图”:两个名称列的联合。在那里添加一个唯一的约束。不像单个表上的相同约束那样坚固:可能会同时写入两个表的竞争条件。但最糟糕的情况是死锁迫使事务回滚。如果所有的写操作都级联到“物化视图”中，则不会出现永久的违规。

Similar to the "dark side" in this related answer:

类似于这个相关答案中的“黑暗面”:

• Can PostgreSQL have a uniqueness constraint on array elements?
• PostgreSQL对数组元素是否具有唯一性约束?

Just that you need triggers for INSERT / UPDATE / DELETE on both tables.

只是需要在两个表上插入/更新/删除触发器。

#2

I had a similar problem where I wanted to maintain a list of items per-company, along with a global list for all companies. If the company number is 0, it is to be treated as global and a new item cannot be inserted for ANY company using that name. The following script (based on the above solution) seems to work:

我也遇到过类似的问题，我希望每个公司都保留一个项目列表，以及所有公司的全球列表。如果公司编号为0，将被视为全局的，任何使用该名称的公司都不能插入新项。下面的脚本(基于上述解决方案)似乎有效:

drop table if exists blech;

CREATE TABLE blech (
        company int,
        name_key text,
        unique (company, name_key)
);

create or replace function f_foobar(new_company int, new_name_key text) returns bool as
$func$
select not exists (
        select 1 from blech b
        where $1 <> 0
        and b.company = 0
        and b.name_key = $2);
$func$ language sql stable;

alter table blech add constraint global_unique_name_key
check (f_foobar(company, name_key)) not valid;

insert into blech values(0,'GLOB1');
insert into blech values(0,'GLOB2');

-- should succeed:
insert into blech values(1,'LOCAL1');
insert into blech values(2,'LOCAL1');

-- should fail:
insert into blech values(1,'GLOB1');

-- should fail:
insert into blech values(0,'GLOB1');