sql - Postgres - 在基于另一列的列中设置唯一约束

Question

我有一个这样的表:

CREATE TABLE schema.mytable
(
  id serial NOT NULL,
  number integer NOT NULL,
  state boolean NOT NULL,
);

我需要创建一组唯一的“数字”，但是状态列必须为真；如果状态列为假，则数字可以重复，这是我需要有效的示例:

id  number      state
1   123         true
2   124         true
3   125         true
4   123         false
5   129         false

如您所见，数字 123 重复出现，但在一种情况下状态为 false，而另一种情况为 true；这是不正确的:

id  number      state
1   123         true
2   124         true
3   125         true
4   123         true (*incorrect)
5   129         false

另外，123有可能以假状态重复两次或更多次；我怎样才能做到这一点？

Answer 1

你不能有一个部分唯一的约束，但你可以创建一个部分唯一的索引，它实现了完全相同的功能:

create unique index mytable_bk on mytable(number) where (state);

Demo on DB FIddle :

insert into mytable(id, number, state) values(1, 123, false);
-- 1 rows affected

insert into mytable(id, number, state) values(1, 123, true);
-- 1 rows affected

insert into mytable(id, number, state) values(1, 123, false);
-- 1 rows affected

insert into mytable(id, number, state) values(1, 123, true);
-- ERROR:  duplicate key value violates unique constraint "mytable_bk"
-- DETAIL:  Key (number)=(123) already exists.