Postgres条件唯一约束

Question

假设我有一个用户表，其中用户是特定租户的成员，并且他们的电子邮件已经按照租户进行了唯一索引，如下所示：

User
id  |  tenant_id  |  email
1      1             [e-mail]
2      1             [e-mail]

该用户被允许访问，因为尽管使用了相同的电子邮件地址，但他们所属的租户不同：

3      2             [e-mail]

由于相同租户下已存在重复的电子邮件，此用户被禁止：

4      2             [e-mail] <--- will throw an error

我们已经通过唯一索引实现了这一点，这部分很容易。

现在假设我想要添加一个全局用户，该用户可以访问所有租户，但仅当电子邮件在表中根本不存在时才能访问。此外，一旦记录存在，无论是租户还是非租户，都将无法使用相同的电子邮件。

为了明确起见，全局用户可以简单地具有空租户ID，但我们可能还会添加一个“全局”布尔值。

有没有办法为这个逻辑编写约束？您不能简单地使电子邮件具有全局独特性，因为它们将无法在不同租户之间重复使用。如果您使用空租户ID进行索引，则Postgres将允许未被租用的用户，如果有相同电子邮件的租用用户。

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

请不要问我为什么要以这种方式处理事情--我的表实际上不是用户，并且我们已经考虑过并放弃了其他架构 :)

提前致谢！

- jack_was_taken

---

为什么不使用(email, tenant)的唯一索引？ - McNets

正如我所说，“他们的电子邮件是根据其租户进行唯一索引”的问题，我们已经解决了第一部分，这很容易。问题在于第二部分。 - jack_was_taken

Answer 1

根据PostgreSQL文档，您可以创建唯一的部分索引，这与在表上创建唯一的部分约束实际上是相同的： CREATE UNIQUE INDEX some_index ON some_table (col_a) WHERE (col_b is null); 使用此技术，您可以为管理员和非管理员用户创建2个单独的唯一索引。

- Sharkman

Answer 2

你可以使用唯一约束来限制这两个字段：

create table myUsers
(
    id int not null,
    tenant int not null,
    email varchar(200) not null,
    UNIQUE(email, tenant)
);

insert into myUsers values
(1, 1, '[e-mail]'),
(2, 1, '[e-mail]');

insert into myUsers values
(3, 2, '[e-mail]');

下一个插入操作将会抛出一个错误：

insert into myUsers values
(4, 2, '[e-mail]');

错误和警告： 23505：重复的键值违反了唯一约束条件“myusers_email_tenant_key”

在此处检查：http://rextester.com/AJZVI34616 对于问题的第二部分：

现在假设我想要能够添加一个全局用户，该用户可以访问所有租户，但前提是电子邮件在表中根本不存在。

一个解决方案可能是为管理员用户保留一个租户：

tenant = 0  <-- admin users

但是唯一约束允许重复的电子邮件，我建议您向此表添加一个rol字段，或者为此目的创建另一个管理员用户表。

在我的情况下，我们使用了两个表，它们都有一个rol字段。

- McNets

---

谢谢您的回答，我应该更清楚地表明我们已经完成了第一部分 - 那很容易。关于第二部分，正如我所指出的，该表实际上不是针对用户的，虽然我们考虑过替代架构，但这是我们代码中最清晰的选择。只是想了解我提到的限制是否 - jack_was_taken

如果可能的话 - jack_was_taken