每个用户最多两个条目的 SQL 唯一约束

Question

是否可以在 SQL 中生成一个唯一约束，允许单个用户 (user_id) 最多启用两个条目 (enabled)？例子如下

user_id  | enabled
------------------
123      | true
123      | true
123      | false
456      | true

以上是有效的，但添加另一个 user_id = 123 和 enabled = true 会失败，因为会有三个条目。另外添加 user_id = 123 和 enabled = false 将是有效的，因为该表仍然满足规则。

Answer 1

您可以通过将另一个 boolean 列添加到 UNIQUE 或 PRIMARY KEY 约束(或 UNIQUE 指数):

CREATE TABLE tbl (
   user_id int
 , enabled bool
 , enabled_first bool DEFAULT true
 , PRIMARY KEY (user_id, enabled, enabled_first)
);

enabled_first 用 true 标记每个实例的第一个。我将其设置为 DEFAULT true 以允许对每个 user_id 的第一个 enabled 进行简单插入 - 没有提及添加的 enabled_first。需要显式 enabled_first = false 才能插入第二个实例。

NULL 值 被我使用的 PK 约束自动排除。请注意，一个简单的 UNIQUE 约束仍然允许 NULL 值，绕过您想要的约束。您必须另外定义所有三列 NOT NULL。见:

• Allow null in unique column

db<> fiddle here

当然，现在这两个true/false值在内部是不一样的，需要调整写操作。这可能会或可能不会被接受。甚至可能是可取的。

受欢迎的副作用:由于最小有效负载(实际数据大小)是每个索引元组 8 个字节，而 bool 值占用 1 个字节而不需要对齐填充，索引仍然与 (user_id) 的最小大小相同, 已启用)。
表类似:添加的boolean 不会增加物理存储。 (可能不适用于具有更多列的表格。)参见:

• Calculating and saving space in PostgreSQL
• Is a composite index also good for queries on the first field?