postgresql - Postgres 条件唯一约束

Question

假设我有一个用户表，其中用户是特定租户的成员，并且他们的电子邮件被唯一索引到他们的租户，如下所示:

User
id  |  tenant_id  |  email
1      1             person1@example.com
2      1             person2@example.com

此用户被允许，因为尽管有重复的电子邮件，但他们在不同的租户:

3      2             person1@example.com

此用户被阻止，因为该电子邮件在同一租户中是重复的:

4      2             person1@example.com <--- will throw an error

我们已经用唯一索引涵盖了这么多——这部分很简单。

现在假设我希望能够添加一个可以访问所有租户的全局用户，但前提是电子邮件根本不存在于表中。此外，一旦记录存在，其他人(无论是否租用)都将无法使用同一电子邮件。

为清楚起见，全局用户可以简单地拥有一个空租户 ID，但我们可能还会添加一个 global bool 值。

有没有办法为这个逻辑编写约束？您不能简单地使电子邮件在全局范围内受到唯一约束，因为它们将无法在租户之间重复，并且如果您使用空租户 ID 进行索引，如果租户用户具有相同的 e，postgres 将允许非租户用户-邮件。

我查看了排除约束和检查，但无法弄清楚如何组合它们(如果 tenant_id 为空，则全局唯一约束电子邮件，并在插入任何时检查具有空租户 ID 和匹配电子邮件的记录记录)。

请不要问我为什么要这样做——我的表实际上不是用户，我们已经考虑并拒绝了其他架构:)

提前致谢!

Answer 1

根据 PostgreSQL 文档，您可以创建 unique partial index这与在您的表上创建唯一的部分约束实际上是一样的:

CREATE UNIQUE INDEX some_index ON some_table (col_a) WHERE (col_b is null);

使用这种技术，您可以为管理员和非管理员用户创建 2 个独立的唯一索引。