sql - 如何在postgres中的多个表中的多个列上定义唯一约束？

Question

我有一些表格，为了简单起见，我们说 2 个:

CREATE TABLE A (
    name varchar
    ...
);
CREATE TABLE B (
    name varchar
    ...
);

这些表具有不同的结构，但都具有相同的列 ( name )

我想在所有这些表中的所有这些列上添加一个唯一约束。

我怎样才能做到这一点？这似乎是一件简单的事情，但到目前为止我的谷歌搜索还没有证明是成功的。大多数现有问题似乎处理同一个表中的多个列。

Answer 1

自 UNIQUE约束不能跨越多个表，您需要创建一个额外的表来存储所有名称。然后每个表将有一个针对额外表的外键。

例如:

create table all_names (
  zone int not null,
  name varchar(20) not null,
  constraint uq1 unique (zone, name),
  constraint uq2 unique (name) -- this is the critical constraint!
);

create table a (
  zone int not null default 1 check (zone = 1),
  name varchar(20) not null,
  constraint fk1 foreign key (zone, name) references all_names (zone, name)
);

insert into all_names (zone, name) values (1, 'Jenny'); -- succeeds
insert into a (name) values ('Jenny'); -- succeeds

create table b (
  zone int not null default 2 check (zone = 2),
  name varchar(20) not null,
  constraint fk2 foreign key (zone, name) references all_names (zone, name)
);

insert into all_names (zone, name) values (2, 'Ivan'); -- succeeds
insert into b (name) values ('Ivan'); -- succeeds

insert into all_names (zone, name) values (2, 'Jenny'); -- fails!
insert into b (name) values ('Jenny'); -- fails!

请注意，现在每个插入都需要额外的 all_names 中的额外插入。 table 。然而，这可以通过使用插入前/插入后触发器(未显示)来自动化(并在幕后发生)。

参见 DB Fiddle 处的运行示例.

如果您实现了触发器，那么您的插入看起来会很简单，如下所示:

insert into a (name) values ('Jenny'); -- succeeds
insert into b (name) values ('Ivan'); -- succeeds
insert into b (name) values ('Jenny'); -- fails!