postgre如何约束SQL中的两列，只允许一列中的值与另一列中的值组合出现?

Question

我在表格中有两列：
- 对于一行，所有两列都需要填充。
- 在 col1 中，允许任何 ID。
- 在 col2 中，一旦设置了特定 ID，则只允许在首次设置时与 col1 中的 ID 组合出现在其他行中。

例：

col1 | col2
------|------
1  |  1        OK
1  |  1        OK
1  |  2        OK
2  |  3        OK
2  |  1        NOK because 1 in col2 is already associated with 1 in col1.
1  |  3        NOK because 3 in col2 is already associated with 2 in col1.

我想使用约束将此规则应用于表。(无需使用第二个表(

有人可以帮助我吗？谢谢！

Answer 1

由于您希望每个 col2 值只允许一个 col1 值，因此您需要一个 col2 表：

create table tcol2
(
col2 int,
col1 int,
primary key (col2)
);

然后，您可以向此表添加一个计数列(这样您将拥有 col1、col2 和计数器，而不是每个 col1 和 col2 多行(，或者使用带有约束的现有表：

alter table mytable add constraint
fk_mytable_tcol2 foreign key (col1, col2) references tcol2 (col1, col2);

Answer 2

您可以使用 EXCLUDE 约束来执行此操作。 您可能希望排除 Col2 = 但 Col1 <>的情况。

create extension btree_gist;
create table foobar (col1 int not null, col2 int not null);
alter table foobar add constraint foobar_ex exclude using gist
(col2 with =, col1 with <>);

Answer 3

我认为你应该修复你的数据模型。 您应该有一个表，每个col2值一行和一个关联的col1值：

create table col2_col1_lookup as (
col2 int primary key,
col1 int unique
);

您可以在此表中查找col1值。