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

How to have a foreign key pointing to two primary keys?

提问by pinch boi triggered af

I'm trying to simplify a database structure, and I have two tables matchesand team_statistics:

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

Here in the team_statisticstable the team_statistics.team_idshould be a foreign key that references matches.teams_idand matches.teams_id1and similarly team_statistics.group_idshould be a foreign key referencing matches.groups_idand matches.groups_id1

在这里，在team_statistics表中team_statistics.team_id应该是一个外键引用matches.teams_id和matches.teams_id1，同样team_statistics.group_id应该是一个外键引用matches.groups_id和matches.groups_id1

How to do this in PostgreSQL?

如何在 PostgreSQL 中做到这一点？

If there are other ways of doing this by having another table between matchesand team_statisticsI'm open for suggestion, but I would still like to know how to have one foreign key referencing two primary keys.

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

回答by Erwin Brandstetter

Rules for FK constraints

FK 约束规则

To answer the question in the title and at the end of your text:

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

"I would still like to know how to have one foreign key referencing two primary keys."

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

That's impossible.

这不可能。

• A FOREIGN KEYconstraint can only point to onetable and each table can only have onePRIMARY KEYconstraint.

• Or you can have multipleFOREIGN KEYconstraints on the same column(s) referencing onePRIMARY KEYof a (different) table each. (Rarely useful.)

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

• 或者您可以对引用一个（不同）表之一的同一列有多个FOREIGN KEY约束。（很少有用。）PRIMARY KEY

However, a single PK or FK canspan multiple columns.
And a FK can reference any explicitly defined unique (set of) column(s) in the target, not just the PK. The manual:

但是，单个 PK 或 FK可以跨越多个列。
并且 FK 可以引用目标中任何明确定义的唯一（一组）列，而不仅仅是 PK。手册：

A foreign key must reference columns that either are a primary key or form a unique constraint.

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

A multicolumn PK or UNIQUEconstraint can only be referenced by a multicolumn FK constraint with matching column types.

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

What you ask

你问什么

Since it is not allowed to use the same column more than once in the column list of a UNIQUEor PRIMARY KEYconstraint, the target list of a FOREIGN KEYcan also not use the same column more than once. But there is nothing to keep us from using the same column more than once in the sourcelist. Herein lies the potential to implement what you are asking (but probably did not mean to):

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

"In the team_statisticstable the team_statistics.team_idshould be a foreign key that references matches.team_idand matches.team_id1"

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

The combination of (team_id, team_id1)in table matcheswould need to be defined UNIQUE. Values in team_statistics.team_idwould be restricted to cases with team = team1in table matchesas logical consequence:

需要定义(team_id, team_id1)表中的组合。值 in将被限制在表中作为逻辑结果的情况：matchesUNIQUEteam_statistics.team_idteam = team1matches

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);

Might even make sense for certain setups, but not yours.

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

What you probably need

你可能需要什么

My educated guess is you want something like this:

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

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

(match_id, team_id)表team_statistics应该是一个外键引用要么(match_id, team_id)或(match_id, team_id1)表matches。

And that's not possible with FK constraints and just two tables. You couldabuse a CHECKconstraint with a fake IMMUTABLEfunction and make it NOT VALID. See chapter "Cheaper with a CHECK constraint" in this answer:

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

• How to avoid a cyclic dependency (circular reference) between 3 tables?

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

But that's advanced trickery and less reliable. Not my suggestion here, so I am not going to elaborate. I suggest to normalizeyour schema in a useful way, like:

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

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)
);

homemarks the home team of the match but, by inclusion in the PK, also restricts to max two teams per match. (PK columns are defined NOT NULLimplicitly.)

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

The optional UNIQUEconstraint on (team_id, match_id)prevents teams from playing against themselves. By using the inverted sequence of index columns (irrelevant for enforcing the rule) this also provides an index complementary to the PK, which is typically also useful. See:

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

• Is a composite index also good for queries on the first field?

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

You couldadd a separate match_team_statistics, but that would just be an optional 1:1 extension to match_teamnow. Alternatively just add columns to match_team.

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

I might add viewsfor typical displays, like:

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

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;

Basic advice:

基本建议：

• How to implement a many-to-many relationship in PostgreSQL?

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

回答by Chris Travers

I think you need to track teams to groups somewhere. Something like:

我认为你需要在某个地方跟踪团队。就像是：

CREATE TABLE team_groups (
    team_id int,
    group_id int,
    primary key (team_id, group_id)
);

then you need the matches table to have two foreign keys against this. Then your statistics table should reference that as well.

那么你需要匹配表有两个外键。那么你的统计表也应该引用它。

You could aso create multiple foreign keys from team_statistics to matches but if you do this, you will run into an inability to grab statistics until a team is both on one side and the other side of a match.

您也可以从 team_statistics 为比赛创建多个外键，但如果这样做，您将无法获取统计数据，直到球队同时位于比赛的一侧和另一侧。

回答by Vao Tsun

If I got your concept right, here is an example:

如果我的概念是正确的，这里是一个例子：

t=# create table matches(team_id int unique,team_id1 int unique);                             CREATE TABLE
t=# insert into matches values (0,0),(1,1),(2,3);                                             INSERT 0 3
t=# create table team_statistics (team_id int);                                               ERROR:  relation "team_statistics" already exists
t=# drop table team_statistics;
DROP TABLE
t=# drop table matches cascade;
DROP TABLE
t=# create table matches(team_id int unique,team_id1 int unique);
CREATE TABLE
t=# insert into matches values (0,0),(1,1),(2,3);
INSERT 0 3
t=# create table team_statistics (team_id int);
CREATE TABLE
t=# alter table team_statistics add constraint fk1 foreign key (team_id) references matches(team_id);
ALTER TABLE
t=# alter table team_statistics add constraint fk2 foreign key (team_id) references matches(team_id1);
ALTER TABLE
t=# insert into team_statistics values (0);
INSERT 0 1
t=# insert into team_statistics values (1);
INSERT 0 1
t=# insert into team_statistics values (2);
ERROR:  insert or update on table "team_statistics" violates foreign key constraint "fk2"
DETAIL:  Key (team_id)=(2) is not present in table "matches".
t=# insert into team_statistics values (3);
ERROR:  insert or update on table "team_statistics" violates foreign key constraint "fk1"
DETAIL:  Key (team_id)=(3) is not present in table "matches".
t=# select * from team_statistics;
 team_id
---------
       0
       1
(2 rows)

t=# select * from matches;
 team_id | team_id1
---------+----------
       0 |        0
       1 |        1
       2 |        3
(3 rows)