全局与局部唯一性的部分唯一索引实现方案问询

我们使用activities表存储企业部门与位置信息，规则如下：

• locationId为NULL时代表全局部门（适用于所有位置）
• locationId非NULL时代表本地部门（仅归属对应位置）

需要实现三个核心唯一性约束：

1. 同一位置下的本地部门名称不能重复
2. 全局部门的名称必须全局唯一
3. 全局部门与任何本地部门的名称不能重复

此前尝试的两个部分唯一索引只能满足前两项约束，无法阻止同名的全局部门与本地部门共存（例如('Name', 'location-1')和('Name', NULL)会被允许），不符合第三项要求。

完整约束方案 #

结合部分唯一索引和排除约束（PostgreSQL原生支持，其他数据库可根据自身特性调整），可同时满足所有约束：

1. 本地部门位置内唯一

创建部分唯一索引，确保同一位置下的部门名称唯一：

CREATE UNIQUE INDEX idx_local_dept_unique ON activities (locationId, name) 
WHERE locationId IS NOT NULL;

2. 全局部门名称唯一

创建部分唯一索引，确保全局部门名称不重复：

CREATE UNIQUE INDEX idx_global_dept_unique ON activities (name) 
WHERE locationId IS NULL;

3. 全局与本地部门名称不冲突

添加排除约束，直接阻止同名的全局部门与本地部门共存：

ALTER TABLE activities
ADD CONSTRAINT exclude_global_local_same_name
EXCLUDE USING btree (name WITH =)
WHERE (locationId IS NULL) WITH (locationId IS NOT NULL);

约束效果验证 #

允许的记录组合：

• ('Name', 'location-1'), ('Name', 'location-2')：同一名称的本地部门归属不同位置，符合规则
• ('Name 1', 'location-1'), ('Name 2', 'location-1')：同一位置下不同名称的本地部门，符合规则
• ('Name 1', 'location-1'), ('Name 2', NULL)：本地部门与不同名称的全局部门，符合规则

不允许的记录组合：

• ('Name', 'location-1'), ('Name', 'location-1')：同一位置重复名称的本地部门，触发第一个索引约束
• ('Name 1', 'location-1'), ('Name 1', NULL)：同名的本地与全局部门，触发排除约束
• ('Name 1', NULL), ('Name 1', NULL)：重复的全局部门，触发第二个索引约束

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