Postgres中如何创建含外键的跨表复合唯一约束？

我正在用Postgres开发一款桌游计分板应用，场景如下：用户Alice可以在应用里给自己和朋友Bob、Caden、David记录分数，这些朋友不用注册账号就能被添加到计分板；之后Caden注册了账号，Alice可以把计分板里的Caden和他的真实账号关联起来。

我把有账号的人定义为user，可后续关联到user的临时记录定义为player，user可以通过添加player来管理计分板。现有表结构如下：

users表 #

players表 #

players_to_moderated_by表 #

players_to_linked_to表 #

现在需要创建两个跨表的唯一约束：

1. moderated_by与players.name的组合唯一，防止同一个user添加同名的player；
2. moderated_by与linked_to的组合唯一，防止同一个user添加多个关联到同一user的player。

由于这两个约束的字段来自不同的表，请问如何用SQL定义这些约束？

Postgres无法直接在多个表上创建原生的UNIQUE CONSTRAINT，但可以通过**唯一索引（Unique Index）**结合关联查询实现跨表唯一性校验。

1. 实现moderated_by + players.name的组合唯一 #

创建基于players_to_moderated_by和players表关联结果的唯一索引：

CREATE UNIQUE INDEX idx_moderator_player_name_unique
ON players_to_moderated_by (moderated_by, (SELECT p.name FROM players p WHERE p.player_id = players_to_moderated_by.player_id));

这个索引会确保同一个moderated_by用户下，不会存在两个关联到同名player的记录。

2. 实现moderated_by + linked_to的组合唯一 #

关联players_to_moderated_by和players_to_linked_to表后创建唯一索引：

CREATE UNIQUE INDEX idx_moderator_linked_user_unique
ON players_to_moderated_by (moderated_by, (SELECT pl.linked_to FROM players_to_linked_to pl WHERE pl.player_id = players_to_moderated_by.player_id));

如果需要仅对已关联user的player生效唯一性校验（允许未关联的player重复），可以添加条件过滤：

CREATE UNIQUE INDEX idx_moderator_linked_user_unique
ON players_to_moderated_by (moderated_by, (SELECT pl.linked_to FROM players_to_linked_to pl WHERE pl.player_id = players_to_moderated_by.player_id))
WHERE EXISTS (SELECT 1 FROM players_to_linked_to pl WHERE pl.player_id = players_to_moderated_by.player_id);

补充说明 #

• 上述索引都是函数型唯一索引，Postgres支持在索引中通过子查询引用其他表的字段；
• 如果需要更严格的一致性校验，可以配合触发器（Trigger），在插入/更新数据时主动校验唯一性并抛出错误；
• 也可以考虑调整表结构优化约束：比如将moderated_by字段直接放到players表中，这样就能直接创建原生唯一约束UNIQUE (moderated_by, name)，性能更好且维护更简单。

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