Wie man eine foreign key verweist auf zwei Primärschlüssel?

Regeln für die FK-constraints

Antwort auf die Frage im Titel und am Ende des Textes:

"Ich würde noch gerne wissen, wie man eine foreign key-Referenzierung zwei primäre Schlüssel."

Das ist unmöglich.

• Einen FOREIGN KEY Einschränkung können nur Punkt-zu - eine Tabelle, und jede Tabelle kann nur eine PRIMARY KEY Einschränkung.

• Oder Sie können mehrere FOREIGN KEY Einschränkungen auf der gleichen Spalte(N) verweisen auf eine PRIMARY KEY einer (anderen) Tabelle jeder. (Nur selten sinnvoll.)

Jedoch, eine einzelne PK oder FK kann über mehrere Spalten erstrecken.

Und ein FK kann auf eine explizit definierte unique (set) Spalte(s) im Ziel, nicht nur die PK. Das Handbuch:

Einen Fremdschlüssel müssen auf Spalten verweisen, die entweder ein Primärschlüssel oder ein unique-Einschränkung.

Einem mehrspaltigen PK oder EINZIGARTIGE Einschränkung kann nur verwiesen werden, die von einem mehrspaltigen FK-constraint mit einer passenden Spaltentypen.

, Was Sie Fragen

Da es ist nicht erlaubt die Verwendung der gleichen Spalte mehr als einmal in der Spaltenliste eines UNIQUE oder PRIMARY KEY Einschränkung der target-Liste des FOREIGN KEY kann auch nicht mit der gleichen Spalte mehr als einmal. Aber es gibt nichts, um uns von der Nutzung der gleichen Spalte mehr als einmal in der Quelle Liste. Hierin liegt das Potenzial, das umzusetzen, was Sie Fragen (aber wahrscheinlich nicht meinst):

"In der team_statistics Tabelle der team_statistics.team_id sollte ein Fremdschlüssel, Referenzen matches.team_id und matches.team_id1"

Die Kombination von (team_id, team_id1) in Tabelle matches hätte definiert werden müssen UNIQUE. Werte in team_statistics.team_id würde auf Fälle beschränkt werden, mit team = team1 in Tabelle matches als logische Konsequenz:

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

Könnte sogar Sinn machen, für bestimmte setups, aber nicht deins.

, Was Sie wahrscheinlich benötigen,

Meine Vermutung ist, Sie wollen so etwas wie dieses:

(match_id, team_id) in Tabelle team_statistics sollte ein Fremdschlüssel, Referenzen entweder (match_id, team_id) oder (match_id, team_id1) in Tabelle matches.

... Und das ist nicht möglich, mit FK-constraints und nur zwei Tabellen. Sie könnte Missbrauch einer CHECK Einschränkung mit einem gefälschten IMMUTABLE - Funktion und machen es NOT VALID. Siehe Kapitel "Günstiger mit einer CHECK-Einschränkung" in dieser Antwort:

• Wie zu vermeiden, eine zyklische Abhängigkeit (Zirkelbezug) zwischen den 3 Tabellen?

Aber das ist fortgeschrittene Tricks und weniger zuverlässig. Nicht mein Vorschlag hier, also ich werde nicht zu erarbeiten. Ich schlage vor, normalisieren Ihr schema in einer nützlichen Art und Weise, wie:

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 markiert die Heimmannschaft das match aber, durch die Aufnahme in die PK, schränkt auch zu max zwei teams pro Spiel. (PK Spalten definiert sind NOT NULL implizit.)

Den optionalen UNIQUE Einschränkung auf (team_id, match_id) verhindert, dass teams aus den spielen gegen sich selbst. Durch die Verwendung der umgekehrten Reihenfolge der index-Spalten (irrelevant für die Durchsetzung der Regel) dies stellt auch ein index ergänzen die PK, die in der Regel auch nützlich. Siehe:

• Ist ein zusammengesetzter index auch gut für Abfragen auf das erste Feld?

Du könnte, eine separate match_team_statistics, aber das wäre nur eine optionale 1:1 Verlängerung match_team jetzt. Alternativ fügen Sie einfach Spalten match_team.

Könnte ich hinzufügen Ansichten für die typisch zeigt, wie:

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;

Grundlegende Ratschläge:

• Wie implementiert man eine viele-zu-viele-Beziehung in PostgreSQL?

Wenn ich Ihr Konzept richtig, hier ist ein Beispiel:

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)