Ein zusammengesetzter PostgreSQL-Fremdschlüssel benötigt einen geeigneten eindeutigen Schlüssel, der die referenzierten Spalten abdeckt. Ein Primärschlüssel, der nur id umfasst, reicht für eine Referenz auf (id, edition) nicht aus, obwohl dieses Paar dadurch logisch eindeutig ist. PostgreSQL: CREATE TABLE

Dieser Leitfaden hilft Entwicklerinnen und Entwicklern, die auf einem Mac im Terminal arbeiten, diese Diskrepanz zu diagnostizieren, die passende Beziehung auszuwählen und das Ergebnis mit node-postgres zu testen. Alle Beispieldatensätze beschreiben fiktive Artikel. Das SQL ist auf PostgreSQL 18 und Fremdschlüssel mit gewöhnlichem Gleichheitsvergleich ausgelegt; PostgreSQL 18 dokumentiert separate Regeln für zeitliche Referenzen mit PERIOD. PostgreSQL: CREATE TABLE

Warum lehnt PostgreSQL die zusammengesetzte Referenz ab?

Angenommen, eine Artikeltabelle hat drei Spalten: id, edition und title. Ihr Primärschlüssel ist id. Eine andere Tabelle speichert eine Artikelreferenz als zwei Werte: article_id und edition.

Die beabsichtigte Beziehung wird durch dieses Fragment einer Tabelleneinschränkung ausgedrückt:

FOREIGN KEY (article_id, edition)
  REFERENCES demo_articles (id, edition)

Diese Deklaration weist PostgreSQL an, die beiden Werte gemeinsam mit einer übergeordneten Zeile abzugleichen. Das referenzierte Ziel muss laut Dokumentation aus den Spalten einer nicht aufschiebbaren Unique- oder Primärschlüsseleinschränkung oder eines nicht partiellen eindeutigen Indexes bestehen. Der vorhandene Primärschlüssel umfasst nur id; die Referenz nennt (id, edition). PostgreSQL: CREATE TABLE

Die mathematische Überlegung hinter dem Einwand ist nachvollziehbar: Wenn keine zwei übergeordneten Zeilen dieselbe id haben können, können sie auch kein identisches Paar aus (id, edition) haben. Diese Folgerung liefert jedoch nicht das passende deklarierte Ziel, das die Fremdschlüsseldefinition verlangt. Für dieses Schema stellt das Hinzufügen von UNIQUE (id, edition) das Ziel bereit. Die zusätzliche Einschränkung ist für die Eindeutigkeit logisch redundant, erfüllt aber einen konkreten Zweck als Ziel der zusammengesetzten Referenz.

Betrachten wir diese beispielhaften übergeordneten Zeilen:

id edition title
101 morning Paper Shapes
102 evening Color Notes

Für das untergeordnete Paar (101, 'morning') gibt es einen Treffer. Das Paar (101, 'evening') hingegen nicht: Seine beiden Werte kommen in der Tabelle vor, aber in unterschiedlichen Zeilen. Das ist der Grund für eine zusammengesetzte Beziehung, wenn beide gespeicherten Werte denselben Artikel beschreiben müssen.

Sollten Sie den eindeutigen Schlüssel hinzufügen oder den Fremdschlüssel vereinfachen?

Legen Sie die Beziehung fest, bevor Sie das Schema ändern. Entscheidend ist, ob die untergeordnete Tabelle neben der Artikelkennung auch eine Ausgabe speichern und durchsetzen muss.

Erforderliches Verhalten Geeignetes Design Folge
Ein untergeordneter Datensatz identifiziert einen Artikel und zeigt dessen aktuelle Ausgabe an article_id speichern; auf das übergeordnete id verweisen Die Ausgabe beim Abfragen aus der übergeordneten Tabelle lesen
Ein untergeordneter Datensatz speichert eine Ausgabe, die zum Artikel passen muss Die zusammengesetzte Referenz beibehalten; UNIQUE (id, edition) zur übergeordneten Tabelle hinzufügen PostgreSQL prüft, ob das gespeicherte Paar gemeinsam vorhanden ist
Dieselbe Artikelnummer kann mehrere Ausgabezeilen identifizieren Die Identität des übergeordneten Datensatzes als (id, edition) modellieren Ein Primärschlüssel nur auf id würde diese übergeordneten Zeilen verhindern

Die letzte Zeile beschreibt ein anderes Datenmodell. Entfernen Sie nicht einfach den einspaltigen Primärschlüssel, nur damit die Fehlermeldung verschwindet: Entscheiden Sie zunächst, ob wiederholte Artikelnummern tatsächlich in die übergeordnete Tabelle gehören. PostgreSQL erlaubt einen zusammengesetzten Primärschlüssel, und eine Tabelle kann nur einen Primärschlüssel haben. PostgreSQL: Constraints

Im folgenden ausführbaren Beispiel bleiben die Artikelkennungen global eindeutig. Der untergeordnete Datensatz speichert bewusst auch eine Ausgabe; daher behält die übergeordnete Tabelle PRIMARY KEY (id) bei und erhält zusätzlich das eindeutige Paar.

Wie können Sie den Fehler auf einem Mac reproduzieren und beheben?

Verwenden Sie eine vorhandene lokale PostgreSQL-18-Datenbank und eine funktionierende Node.js-Installation; diese Anleitung setzt voraus, dass beides bereits verfügbar ist. Öffnen Sie Terminal und erstellen Sie ein neues Projektverzeichnis. Installieren Sie den Treiber mit dem dokumentierten Paketbefehl. node-postgres: Welcome

mkdir composite-fk-demo
cd composite-fk-demo
npm init -y
npm install pg

Speichern Sie das folgende vollständige Programm als demo.mjs. Es erstellt temporäre Tabellen, zeigt das abgelehnte Schema, fügt den passenden Schlüssel hinzu und testet gültige und ungültige Artikelpaare. Alle Anweisungen verwenden denselben Client, da node-postgres verlangt, dass eine Transaktion bei derselben Clientinstanz bleibt. node-postgres: Transactions

Die Hilfsfunktion verwendet für jeden erwarteten Fehler einen Savepoint, damit die Transaktion fortgesetzt werden kann. PostgreSQL dokumentiert, dass ROLLBACK TO SAVEPOINT die Befehle nach diesem Savepoint rückgängig macht und die Fortsetzung der Arbeit ermöglicht. PostgreSQL: ROLLBACK TO SAVEPOINT

import pg from 'pg';
const { Client } = pg;
const client = new Client();

const childDDL = `CREATE TEMP TABLE demo_article_links (
  link_id integer PRIMARY KEY,
  article_id integer NOT NULL,
  edition text NOT NULL,
  FOREIGN KEY (article_id, edition)
    REFERENCES demo_articles (id, edition)
)`;

async function expectError(sql, values, code) {
  await client.query('SAVEPOINT expected_error');
  try {
    await client.query(sql, values);
  } catch (error) {
    await client.query('ROLLBACK TO SAVEPOINT expected_error');
    await client.query('RELEASE SAVEPOINT expected_error');
    if (error.code !== code) throw error;
    console.log(`Expected error: ${code}`);
    return;
  }
  await client.query('ROLLBACK TO SAVEPOINT expected_error');
  await client.query('RELEASE SAVEPOINT expected_error');
  throw new Error(`Expected SQLSTATE ${code}, but query succeeded`);
}

try {
  await client.connect();
  await client.query('BEGIN');
  await client.query(`CREATE TEMP TABLE demo_articles (
    id integer PRIMARY KEY,
    edition text NOT NULL,
    title text NOT NULL
  )`);

  await expectError(childDDL, [], '42830');

  await client.query(`ALTER TABLE demo_articles
    ADD CONSTRAINT demo_articles_id_edition_key
    UNIQUE (id, edition)`);
  await client.query(childDDL);

  await client.query(`INSERT INTO demo_articles (id, edition, title)
    VALUES ($1, $2, $3), ($4, $5, $6)`,
    [101, 'morning', 'Paper Shapes', 102, 'evening', 'Color Notes']);

  await client.query(`INSERT INTO demo_article_links
    (link_id, article_id, edition) VALUES ($1, $2, $3)`,
    [1, 101, 'morning']);

  await expectError(`INSERT INTO demo_article_links
    (link_id, article_id, edition) VALUES ($1, $2, $3)`,
    [2, 101, 'evening'], '23503');

  const result = await client.query(`SELECT l.link_id, a.title, a.edition
    FROM demo_article_links AS l
    JOIN demo_articles AS a
      ON a.id = l.article_id AND a.edition = l.edition
    ORDER BY l.link_id`);
  console.table(result.rows);
} catch (error) {
  console.error(error.code ?? error.message);
  process.exitCode = 1;
} finally {
  try { await client.query('ROLLBACK'); }
  finally { await client.end(); }
}

Das Programm übergibt Artikelwerte separat über $1, $2 und die folgenden Platzhalter gemäß der parametrisierten Abfrage-API des Treibers. Tabellen- und Spaltennamen bleiben im SQL fest vorgegeben; PostgreSQL-Parameter können keine Bezeichner ersetzen. node-postgres: Queries

Führen Sie das Programm mit den Verbindungseinstellungen für Ihre vorhandene lokale Datenbank aus. Dieser Aufruf setzt voraus, dass die Datenbank article_demo heißt, der Server auf Port 5432 lauscht und Ihre vorhandenen lokalen Standardverbindungseinstellungen funktionieren. Ändern Sie bei Bedarf den Datenbanknamen oder Port. node-postgres liest die PostgreSQL-Verbindungsumgebungsvariablen. node-postgres: Connecting

PGHOST=localhost PGPORT=5432 PGDATABASE=article_demo node demo.mjs

Das JavaScript wurde bei der Vorbereitung dieses Artikels einer Syntaxprüfung unterzogen. Das Datenbankverhalten wurde anhand der Dokumentation überprüft; das Programm wurde hier nicht auf einem PostgreSQL-Server ausgeführt.

Was zeigen die beiden Fehler und die abschließende Abfrage?

Unter den genannten Voraussetzungen werden folgende Meldungen erwartet:

Erwarteter Fehler: 42830
Erwarteter Fehler: 23503

Der erste Fehler tritt beim Erstellen der untergeordneten Tabelle auf, bevor Artikelzeilen eingefügt werden. PostgreSQL ordnet 42830 als invalid_foreign_key ein. Nachdem der passende eindeutige Schlüssel zur übergeordneten Tabelle hinzugefügt wurde, ist dieselbe Definition der untergeordneten Tabelle gültig. PostgreSQL: Error Codes

Der zweite Fehler tritt beim Einfügen von (101, 'evening') auf. Das Schema ist nun gültig, aber für das angegebene Paar gibt es keine übergeordnete Zeile. PostgreSQL ordnet 23503 als foreign_key_violation ein. Anhand des Fehlercodes lässt sich diese Zeilenabweichung von der vorherigen Schemaabweichung unterscheiden, ohne von lokalisierten Fehlermeldungen abhängig zu sein. PostgreSQL: Error Codes

Die abschließende Abfrage sollte eine Zeile zurückgeben: Link 1, Titel Paper Shapes, Ausgabe morning. Ihre Join-Bedingung umfasst beide Bestandteile der Beziehung. Damit beschreibt die Abfrage dasselbe Paar, dessen Einhaltung die Einschränkung erzwingt.

Zwei separate Fremdschlüssel würden eine andere Regel ausdrücken. Selbst wenn edition einen eigenen geeigneten eindeutigen Schlüssel hätte, würden unabhängige Referenzen auf id und edition zulassen, dass jeder Wert zu einer anderen übergeordneten Zeile passt. Das Arbeitsbeispiel macht den Unterschied sichtbar: Beide Einzelwerte des abgelehnten Paars existieren, ihre Kombination jedoch nicht.

Der abschließende ROLLBACK verwirft bewusst die Demonstration einschließlich ihrer Tabellendefinitionen. Um das Design auf dauerhafte Tabellen zu übertragen, nehmen Sie die Schemaänderung in Ihrem üblichen Migrationsprozess vor; durch das Kopieren dieses Programms mit temporären Tabellen entsteht kein dauerhaftes Anwendungsschema.

Was sollten Sie vor der Änderung eines bestehenden Schemas prüfen?

Beginnen Sie mit der Liste der referenzierten Spalten. Ermitteln Sie den übergeordneten Schlüssel, der diese Liste tatsächlich abdeckt, statt sich auf einen Indexnamen oder darauf zu verlassen, dass die aktuellen Zeilen zufällig eindeutig sind. In diesem Beispiel ist die benötigte Ergänzung die im Programm gezeigte benannte Einschränkung UNIQUE (id, edition).

Das Hinzufügen einer Unique-Einschränkung erstellt einen eindeutigen B-Baum-Index. Berücksichtigen Sie diesen zusätzlichen Index, wenn Sie zwischen dem zusammengesetzten Design und dem Speichern nur von article_id wählen. Die Fremdschlüsseldeklaration selbst erstellt nicht automatisch einen Index auf den Spalten der untergeordneten Tabelle. PostgreSQL: Constraints

Prüfen Sie vor dem Hinzufügen eines Fremdschlüssels zu einer bestehenden untergeordneten Tabelle, ob ihre gespeicherten Paare zu übergeordneten Zeilen passen. Ein gültiger Zielschlüssel erfüllt die Schemaanforderung, korrigiert aber keine abweichenden Werte in der untergeordneten Tabelle. Ein gewöhnliches ALTER TABLE ... ADD CONSTRAINT prüft bestehende Zeilen. PostgreSQL unterstützt auch das Hinzufügen eines Fremdschlüssels als NOT VALID mit anschließender Ausführung von VALIDATE CONSTRAINT; dadurch wird die Prüfung bestehender Zeilen aufgeschoben, während die Einschränkung für nachfolgende Einfügungen oder Aktualisierungen gilt. PostgreSQL: ALTER TABLE

Behandeln Sie die Validierung getrennt von der Eignung des Ziels. NOT VALID stellt den fehlenden übergeordneten Schlüssel nicht bereit. Es ist auch kein Weg, eine Unique-Einschränkung hinzuzufügen, ohne die Eindeutigkeit nachzuweisen: PostgreSQL 18 erlaubt diese Option nur für Fremdschlüssel-, Check- und Not-Null-Einschränkungen. PostgreSQL: ALTER TABLE

Welche Besonderheiten können das Ergebnis ändern?

Ein partieller eindeutiger Index eignet sich nicht als Ziel für den hier beschriebenen gewöhnlichen Fremdschlüssel. Ebenso erfüllt eine aufschiebbare referenzierte Unique-Einschränkung nicht die dokumentierte Anforderung an eine nicht aufschiebbare Einschränkung. Verwenden Sie für dieses Beispiel den einfachen Standardwert UNIQUE (id, edition). PostgreSQL: CREATE TABLE

Auch der Umgang mit NULL-Werten ist wichtig. Beim standardmäßigen Abgleichverhalten ist für einen zusammengesetzten Fremdschlüssel kein Treffer in der übergeordneten Tabelle erforderlich, wenn eine beliebige referenzierende Komponente NULL ist. Im Beispiel sind beide Komponenten der untergeordneten Tabelle als NOT NULL markiert, damit jedes gespeicherte Paar übereinstimmen muss. Wenn eine optionale Beziehung beabsichtigt ist, erlaubt MATCH FULL eine vollständig aus NULL-Werten bestehende Referenz, weist aber eine teilweise aus NULL-Werten bestehende Referenz zurück. PostgreSQL: Constraints

Der entscheidende Test betrifft das Paar selbst: Kann die untergeordnete Tabelle eine Artikelkennung und eine Ausgabe angeben, die zu unterschiedlichen Zeilen gehören? Wenn das fehlschlagen muss, behalten Sie die zusammengesetzte Beziehung bei und ergänzen Sie einen passenden übergeordneten Schlüssel. Wenn die untergeordnete Tabelle nur einen Artikel identifizieren muss, verweisen Sie auf id und lesen die Ausgabe dieses Artikels bei der Abfrage aus.

Quellen