Postgresql 约束在 2 列上，因此"col_a"不能共享任何"col_b"值

Question

我正在构建一个具有系统条形码以及自定义条形码选项的数据库。我对custom_barcode和system_barcode有一个唯一的约束。

我想做的是，如果custom_barcode的值为"123"，没有其他行可以有值为"123"的custom_barcode或system_barcode。我不是试图创建一个2列的唯一约束，如果custom_barcode是"123"，system_barcode是"456"，则该组合就不可能再次存在。有可能实现这一点吗？我想知道这是否需要用触发器来实现。

Answer 1

如果我正确地遵循您的建议，您可以使用检查约束和基于数组的排除约束：

create table mytable (
id serial primary key,
custom_barcode int,
system_barcode int,
check (custom_barcode <> system_barcode),
exclude using gist((array[custom_barcode, system_barcode]) with &&)
);

check约束确保不能将相同的值分配给同一行上的两列。

排除约束构建一个包含这两个代码的数组，并确保没有其他行的数组与当前行重叠。

这是演示：

insert into mytable (custom_barcode, system_barcode) values (1, 1);
-- ERROR:  new row for relation "mytable" violates check constraint "mytable_check"
insert into mytable (custom_barcode, system_barcode) values (2, 3);
-- ok
insert into mytable (custom_barcode, system_barcode) values (4, 2);
-- ERROR:  conflicting key value violates exclusion constraint "mytable_array_excl"
-- DETAIL:  Key ((ARRAY[custom_barcode, system_barcode]))=({4,2}) conflicts with existing key ((ARRAY[custom_barcode, system_barcode]))=({2,3}).