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

Question

I'm trying to simplify a database structure, and I have two tables matches and 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 should be a foreign key referencing matches.groups_id and matches.groups_id1

How to do this in PostgreSQL?

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.

Answer 1

Rules for FK constraints

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 KEY constraint can only point to one table and each table can only have one PRIMARY KEY constraint.

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

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.

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

What you ask

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

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

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.

What you probably need

My educated guess is you want something like this:

(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.

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:

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

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

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:

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

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;

Basic advice:

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