PostgreSQL用户表特殊唯一性约束实现：多角色多组织权限管控

完全可以通过PostgreSQL的**部分唯一索引（Partial Unique Index）**实现这个需求，具体方案如下：

1. 清理原有约束 #

先移除原有的email字段全局唯一约束：

ALTER TABLE users DROP CONSTRAINT IF EXISTS users_email_key;

2. 创建差异化约束 #

我们需要创建两个互补的唯一索引：

针对employee/master角色的约束

强制这类角色的用户只能归属一个组织（即email全局唯一）：

CREATE UNIQUE INDEX idx_users_email_non_consultant ON users (email)
WHERE role IN ('employee', 'master');

全局的email+组织约束

确保所有角色的用户，在同一个组织下只能存在一条记录（避免重复创建），同时允许consultant角色关联多个不同组织：

CREATE UNIQUE INDEX idx_users_email_organisation ON users (email, organisation_id);

约束效果说明 #

• 对于employee或master角色：
  • 同一个email只能插入一次（不管组织ID），确保用户仅归属一个组织；
  • 同时同一个email+组织ID的组合也不会重复，避免无效重复数据。
• 对于consultant角色：
  • 允许同一个email关联多个不同的organisation_id；
  • 但同一个email+同一个organisation_id的组合依然无法重复插入，保证数据唯一性。

内容的提问来源于stack exchange，提问作者Maharshi