如何使外键指向两个主键? [英] How to have a foreign key pointing to two primary keys?

问题描述

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

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

在team_statistics表中，team_statistics.team_id应该是引用matches.teams_id和matches.teams_id1的外键，并且类似地team_statistics.group_id应该是引用matches.groups_id和matches.groups_id1

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 should be a foreign key referencing matches.groups_id and matches.groups_id1

如何在PostgreSQL中做到这一点?

How to do this in PostgreSQL?

如果还有其他方法可以通过在matches和team_statistics之间设置另一个表来实现这一点，我很乐意提出建议，但我仍然想知道如何使用一个外键引用两个主键.

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

推荐答案

FK约束规则

要在标题和文本结尾处回答问题:

Rules for FK constraints

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

我仍然想知道如何使用一个外键引用两个主键."

那不可能.

• A FOREIGN KEY 约束只能指向 一个 表，并且每个表只能具有 一个

• A FOREIGN KEY constraint can only point to one table and each table can only have one PRIMARY KEY constraint.

或者您可以在同一列上具有多个 FOREIGN KEY约束，每个约束分别引用(不同)表的一个 PRIMARY KEY. (非常有用.)

Or you can have multiple FOREIGN KEY constraints on the same column(s) referencing one PRIMARY KEY of a (different) table each. (Rarely useful.)

但是，单个PK或FK 可以跨越多列.
FK可以引用目标中任何明确定义的唯一(一组)列，而不仅仅是PK. 手册:

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

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

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

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

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

由于不允许在UNIQUE或PRIMARY KEY约束的列列表中多次使用同一列，因此FOREIGN KEY的目标列表也不能多次使用同一列.但是，没有什么可以阻止我们在 source 列表中多次使用同一列.这就是实现您所要询问的内容的潜力(但可能并不意味着这样做):

Since it is not allowed to use the same column more than once in the column list of a UNIQUE or PRIMARY KEY constraint, the target list of a FOREIGN KEY can 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 source list. Herein lies the potential to implement what you are asking (but probably did not mean to):

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

"In the team_statistics table the team_statistics.team_id should be a foreign key that references matches.team_id and matches.team_id1"

需要在表matches中定义(team_id, team_id1)的组合. team_statistics.team_id中的值将被限制为表matches中具有team = team1的情况作为逻辑结果:

The combination of (team_id, team_id1) in table matches would need to be defined UNIQUE. Values in team_statistics.team_id would be restricted to cases with team = team1 in table matches as logical consequence:

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.

我有根据的猜测是您想要这样的东西:

My educated guess is you want something like this:

(match_id, team_id)应该是引用表matches中的 (match_id, team_id) 或 (match_id, team_id1)的外键. /em>

(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约束的更便宜的产品"一章:

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

• 如何为了避免3个表之间存在循环依赖性(循环引用)?

但这是先进的欺骗手段，可靠性较低.这不是我的建议，因此我不再赘述.我建议 规范化 一种有用的方式，例如:

But that's advanced trickery and less reliable. Not my suggestion here, so I am not going to elaborate. I suggest to normalize your 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)
);

home标记了比赛的主队，但由于包含在PK中，因此每场比赛最多只能限制两队. (PK列是NOT NULL隐式定义的.)

home marks 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 NULL implicitly.)

(team_id, match_id)上的可选UNIQUE约束可防止团队对抗自己.通过使用索引列的倒置序列(与执行规则无关)，这也提供了与PK互补的索引，这通常也是有用的.参见:

The optional UNIQUE constraint 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:

• 复合索引是否也适合在第一个字段上进行查询?

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

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

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

I might add views for 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;

基本建议:

• 如何实施许多PostgreSQL中有多对多关系?

这篇关于如何使外键指向两个主键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！