sql - postgres 可以检查*有条件地*限制重复项吗？

Question

我必须实现一条规则，即只有一个具有给定 ID 的“组”一次可以具有“进行中”或“问题”状态。是否可以在 Postgres 检查中表示这一点，或者我必须求助于应用程序中的逻辑？

例如:

INSERT INTO group (group_id, status) VALUES (1, 'In Progress'); -- okay
INSERT INTO group (group_id, status) VALUES (2, 'Problem'); -- okay
INSERT INTO group (group_id, status) VALUES (3, 'In Progress'); -- okay
INSERT INTO group (group_id, status) VALUES (4, 'Problem'); -- okay
INSERT INTO group (group_id, status) VALUES (1, 'Something else'); -- okay
INSERT INTO group (group_id, status) VALUES (2, 'Foo bar'); -- okay

INSERT INTO group (group_id, status) VALUES (1, 'In Progress'); -- should fail
INSERT INTO group (group_id, status) VALUES (1, 'Problem'); -- should fail
INSERT INTO group (group_id, status) VALUES (2, 'In Progress'); -- should fail
INSERT INTO group (group_id, status) VALUES (2, 'Problem'); -- should fail

Answer 1

我认为您需要一个部分唯一索引。

CREATE UNIQUE INDEX group_status_unq_idx 
ON "group"(group_id, status) WHERE (status IN ('In Progress', 'Problem'));

但是，根据您的描述，我不清楚为什么第二个预期失败会失败。对于任何给定的 group_id，您是否希望仅允许 In Progress 或 Problem 之一？如果是这样，你可以这样写:

CREATE UNIQUE INDEX group_status_unq_idx
ON "group"(group_id) WHERE (status IN ('In Progress', 'Problem'));

... 从部分唯一索引中省略 status 并将其仅用于谓词。参见 this SQLFiddle .

请注意，这不能表示为 UNIQUE 约束，因为唯一约束不采用谓词。 PostgreSQL 唯一约束是使用(非部分)UNIQUE 索引实现的，但它还会创建仅创建索引不会创建的元数据条目。部分唯一索引的工作方式类似于带有谓词的唯一约束，但它不会通过 information_schema 的约束信息等元数据发现。