database - 添加约束以使每组行的列唯一

Postgres表中有一列status，该列只能采用两个值:Active和Inactive。

其中一列名为userid。该表可以包含具有相同userid的多行，但最多其中一行可以具有status = 'Active'。我只需要一个status或不使用Active作为userid即可。如何在这种情况下创建约束？我从Postgres文档中找不到任何帮助。

最佳答案

像@lad2025 commented一样，status应该确实是 boolean 。便宜，干净。
无论哪种方式，您都可以使用partial unique index来施加规则:
要在整个表中使用status = 'Active' 允许零行或一行:

CREATE UNIQUE INDEX tbl_active_uni ON tbl (status)
WHERE status = 'Active';

要允许每行status = 'Active' 带有userid 的零行或一行，使userid成为索引列:

CREATE UNIQUE INDEX tbl_userid_active_uni ON tbl (userid)
WHERE status = 'Active';

请注意，userid IS NULL不会触发唯一违规，因为两个NULL值从不视为相等。在这种情况下，userid必须为set NOT NULL 。

How to add a conditional unique index on PostgreSQL

Create unique constraint with null columns

为什么要索引而不是约束？
解决您的question in the comment:这是一个索引，而不是 CONSTRAINT 。
第一种情况的索引是 tiny ，保留一行或不一行。
第二种情况的索引每个现有的userid保留一行，但这是最便宜，最快的方式，除了干净而且安全。在任何情况下，您都需要一个索引来检查其他行，以加快此速度。
您不能对其他行进行CHECK约束检查-至少不能以一种干净，可靠的方式进行检查。在这种情况下，我有多种方式当然不建议:

Trigger vs. check constraint

How to avoid a cyclic dependency (circular reference) between 3 tables?

Disable all constraints and table checks while restoring a dump

如果在UNIQUE上使用(userid, status)约束(该约束也通过在后台使用唯一索引来实现!)，则不能使其部分化，并且所有组合都必须是唯一的。如果在status IS NULL情况以外的所有情况下都使用'Active'，则仍可以使用此功能。但这实际上会强加更大的索引，包括所有行。

关于database - 添加约束以使每组行的列唯一，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/34495479/