如何让外键指向两个主键？

Question

我正在尝试简化数据库结构，并且我有两个表 matches 和 team_statistics ：

Here in the team_statistics table the team_statistics.team_id should be a foreign key that references matches.teams_id and matches.teams_id1 and similarly team_statistics.group_id 应该是引用 matches.groups_id 和 matches.groups_id1 的外键

如何在 PostgreSQL 中做到这一点？

如果有其他方法可以通过在 matches 和 team_statistics 之间设置另一个表来做到这一点，我愿意接受建议，但我仍然想知道如何让一个外键引用两个主键。

原文由 pinch boi triggered af 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

FK 约束规则

要回答标题和文本末尾的问题：

“我仍然想知道如何让一个外键引用两个主键。”

这不可能。

• 一个 FOREIGN KEY 约束只能指向 一个 表，每个表只能有 一个 PRIMARY KEY 约束。

• 或者，您可以在同一列上拥有 多个 FOREIGN KEY 约束，每个都引用 _一个_（不同）表中的一个 PRIMARY KEY 。 （很少有用。）

但是，单个 PK 或 FK 可以 跨越多个列。

FK 可以引用目标中任何明确定义的唯一（组）列，而不仅仅是 PK。 手册：

外键必须引用作为主键或形成唯一约束的列。

多列 PK 或 UNIQUE 约束只能由具有匹配列类型的多列 FK 约束引用。

你问什么

由于不允许在 UNIQUE 或 PRIMARY KEY 约束的列列表中多次使用同一列，因此 FOREIGN KEY 的目标列表也可以不要多次使用同一列。但是没有什么可以阻止我们在 源 列表中多次使用同一列。这就是实现您所要求的内容的潜力（但可能并非有意）：

“在 team_statistics 表中 team_statistics.team_id 应该是一个外键引用 matches.team_id 和 matches.team_id1 ”

需要定义 matches 表中的组合 (team_id, team_id1) UNIQUE 。 team_statistics.team_id 中的值将仅限于表 team = team1 中的 matches 作为逻辑结果：

 ALTER TABLE matches
ADD constraint matches_teams_groups_uni UNIQUE (team_id, team_id1);

ALTER TABLE team_statistics
  ADD constraint team_statistics_team_group fkey
  FOREIGN KEY (team_id, team_id)  -- same column twice!
  REFERENCES matches(team_id, team_id1);

甚至可能对某些设置有意义，但不是你的。

你可能需要什么

我有根据的猜测是你想要这样的东西：

(match_id, team_id) in table team_statistics should be a foreign key that references either (match_id, team_id) or (match_id, team_id1) in table matches .

这对于 FK 约束和只有两个表是不可能的。您 可以 使用假的 IMMUTABLE 函数滥用 CHECK 约束并使其成为 NOT VALID 。请参阅此答案中的“使用 CHECK 约束更便宜”一章：

• 如何避免 3 个表之间的循环依赖（循环引用）？

但这是高级诡计，不太可靠。这里不是我的建议，所以我不打算详细说明。我建议以一种有用的方式 _规范_ 您的架构，例如：

 CREATE TABLE team (team_id serial PRIMARY KEY
                 , team text NOT NULL UNIQUE);     -- add more attributes for team

CREATE TABLE match (match_id serial PRIMARY KEY);  -- add more attributes for match

CREATE TABLE match_team (
   match_id  int  REFERENCES match  -- short notation for FK
 , team_id   int  REFERENCES team
 , home boolean                     -- TRUE for home team, FALSE for away team
 , innings_score int
 -- more attributes of your original "team_statistics"
 , PRIMARY KEY (match_id, team_id, home)  -- !!! (1st column = match_id)
 , UNIQUE (team_id, match_id)             -- optional, (1st column = team_id)
);

home 标志着比赛的主队，但通过包含在 PK 中，也限制 _每场比赛最多两支球队_。 （PK 列被隐式定义为 NOT NULL 。）

可选的 UNIQUE 约束 (team_id, match_id) 防止球队与自己比赛。通过使用索引列的倒序（与执行规则无关），这也提供了与 PK 互补的索引，这通常也很有用。看：

• 复合索引是否也适用于第一个字段的查询？

您 可以 添加一个单独的 match_team_statistics ，但这只是 match_team 现在的可选 1:1 扩展。或者，只需将列添加到 match_team 。

我可能会为典型显示添加 视图，例如：

 CREATE VIEW match_result AS
SELECT m.match_id
     , concat_ws(' : ', t1.team, t2.team) AS home_vs_away_team
     , concat_ws(' : ', mt1.innings_score, mt2.innings_score) AS result
FROM   match           m
LEFT   JOIN match_team mt1 ON mt1.match_id = m.match_id AND mt1.home
LEFT   JOIN team       t1  ON t1.team_id = mt1.team_id
LEFT   JOIN match_team mt2 ON mt2.match_id = m.match_id AND NOT mt2.home
LEFT   JOIN team       t2  ON t2.team_id = mt2.team_id;

基本建议：

• 如何在 PostgreSQL 中实现多对多关系？

原文由 Erwin Brandstetter 发布，翻译遵循 CC BY-SA 4.0 许可协议