Ein PostgreSQL-Fremdschlüssel mit NOT VALID überspringt die anfängliche Prüfung bestehender Zeilen und erzwingt neue Referenzen. Ältere nicht passende Zeilen bleiben bestehen, bis sie korrigiert und validiert wurden. PostgreSQL 17: ALTER TABLE

Diese Anleitung richtet sich an Entwickler, die im Terminal auf einem Mac arbeiten, einer befüllten Tabelle einen Fremdschlüssel hinzufügen und die Ergebnisse verstehen möchten. Das vollständige node-postgres-Beispiel verwendet ausschließlich synthetische Artikeldaten und verfolgt eine nicht passende Referenz von der Erstellung der Einschränkung bis zur Korrektur und erfolgreichen Validierung.

Was geschieht mit bestehenden Zeilen und neuen Schreibvorgängen?

Betrachten wir zwei Tabellen: articles enthält Artikel-IDs und Titel, und article_links enthält Verweise auf diese IDs. Eine Verknüpfung passt nicht, wenn für ihre nicht-null-article_id kein entsprechender Artikel vorhanden ist. Wird der Fremdschlüssel mit NOT VALID hinzugefügt, kann diese ältere Abweichung bestehen bleiben, während die Einschränkung für nachfolgende Schreibvorgänge gilt. PostgreSQL 17: ALTER TABLE

Vorgang nach dem Hinzufügen der Einschränkung Ergebnis in diesem Beispiel
Eine ältere nicht passende Verknüpfung unverändert lassen Sie bleibt gespeichert
Eine Verknüpfung einfügen, die auf einen vorhandenen Artikel verweist Der Vorgang ist erfolgreich
Eine Verknüpfung einfügen, die auf einen fehlenden Artikel verweist Der Vorgang schlägt fehl
Die Artikel-ID einer Verknüpfung in eine andere fehlende ID ändern Der Vorgang schlägt fehl
Validieren, während eine nicht passende Verknüpfung bestehen bleibt Die Validierung schlägt fehl
Alle nicht passenden Referenzen korrigieren und anschließend validieren Die Validierung kann erfolgreich sein

Diese Ergebnisse entsprechen der dokumentierten Trennung zwischen dem Hinzufügen und Validieren der Einschränkung. PostgreSQL 17: ALTER TABLE

Eine wichtige Ausnahme betrifft Aktualisierungen, bei denen der Wert des Fremdschlüssels unverändert bleibt. In PostgreSQL 18 kann die Implementierung die Prüfung des Fremdschlüssels überspringen, wenn die alten und neuen Schlüssel als gleich gelten. Daher kann eine Änderung nur der Beschriftung einer älteren nicht passenden Verknüpfung erfolgreich sein, ohne dass ihre Referenz korrigiert wird. Die Implementierung enthält außerdem einen Sonderfall für eine Zeile, die von der aktuellen Transaktion eingefügt wurde. Dieses Verhalten sollte deshalb nicht auf jede Folge von Aktualisierungen verallgemeinert werden. PostgreSQL-18-Implementierung von Fremdschlüsseln

Die praktische Folge: Erfolgreiche Änderungen an Beschriftungen sind kein zuverlässiger Beleg dafür, dass die Bereinigung abgeschlossen ist. Prüfen Sie nicht passende Referenzen direkt und verwenden Sie die Validierung, um festzustellen, dass die Einschränkung für die gesamte Tabelle gilt.

Welche PostgreSQL-Version und Tabellenstruktur sind erforderlich?

Das folgende Beispiel verwendet gewöhnliche, nicht partitionierte Tabellen und ist für PostgreSQL 17 und 18 gedacht. Bei partitionierten Tabellen gibt es eine wichtige Versionsgrenze: PostgreSQL 17 erlaubt ausdrücklich nicht, Fremdschlüssel auf partitionierten Tabellen als NOT VALID zu deklarieren; PostgreSQL 18 hat diese Möglichkeit eingeführt. Prüfen Sie die Serverversion, statt die Unterstützung anhand Ihres installierten Node-Pakets abzuleiten. PostgreSQL 17: ALTER TABLE, Versionshinweise zu PostgreSQL 18

Für eine partitionierte referenzierende Tabelle unter PostgreSQL 17 besteht der unterstützte direkte Weg darin, die Referenzen zu korrigieren und den Fremdschlüssel regulär hinzuzufügen, sodass die anfängliche Validierungsprüfung ausgeführt wird. Ist eine gestufte Validierung auf der partitionierten übergeordneten Tabelle unerlässlich, bietet PostgreSQL 18 die dokumentierte Möglichkeit. In dieser Anleitung bleibt die Tabellenstruktur einfach, damit Details der Partitionshierarchie das Verhalten der Zeilen nicht verschleiern.

Auch der referenzierte Schlüssel benötigt eine geeignete Eindeutigkeitsgarantie. Hier ist articles.article_id ein Primärschlüssel. Die referenzierende Spalte lässt absichtlich NULL zu: Gemäß der Standardregel MATCH SIMPLE muss es für eine Nullreferenz keinen passenden Artikel geben. Fügen Sie separat NOT NULL hinzu, wenn jede Verknüpfung einen Artikel identifizieren muss. PostgreSQL 18: CREATE TABLE

Ein vollständiges synthetisches Beispiel auf Ihrem Mac ausführen

Sie benötigen eine laufende PostgreSQL-Datenbank, eine bereits vorhandene lokale Verbindungskonfiguration und eine von node-postgres unterstützte Node.js-Version. Installieren Sie das Paket in einem Projektverzeichnis im Terminal mit dem folgenden Befehl und speichern Sie dann das vollständige Programm als not-valid-demo.mjs. Die offizielle node-postgres-Einführung dokumentiert die Paketinstallation. node-postgres-Dokumentation

npm install pg

Das Programm verwendet new pg.Client(), das PostgreSQL-Verbindungsumgebungsvariablen wie PGHOST, PGPORT, PGDATABASE und PGUSER ausliest. Verwenden Sie die Einstellungen, mit denen Sie bereits eine Verbindung zu Ihrer lokalen Datenbank herstellen. node-postgres: Verbindung herstellen

Beide Tabellen sind temporär und existieren nur während der Sitzung dieser Verbindung. PostgreSQL verlangt, dass Fremdschlüssel temporärer Tabellen auf temporäre Tabellen verweisen. Daher erstellt das Programm beide Seiten zusammen. PostgreSQL 18: CREATE TABLE

Für jeden Vorgang wird eine eigene Query mit await ausgeführt. Werte werden über node-postgres-Parameter wie $1 und $2 übergeben; die Tabellen- und Einschränkungsnamen sind in der SQL-Abfrage fest vorgegeben. node-postgres: Abfragen

import pg from 'pg';

const client = new pg.Client({ connectionTimeoutMillis: 5000 });
const validate = `ALTER TABLE pg_temp.article_links
  VALIDATE CONSTRAINT article_links_article_fk`;

async function expectForeignKeyError(label, sql, values = []) {
  try {
    await client.query(sql, values);
  } catch (error) {
    if (error.code !== '23503' ||
        error.constraint !== 'article_links_article_fk') throw error;
    console.log(label + ': 23503 (expected)');
    return;
  }
  throw new Error(label + ': unexpectedly succeeded');
}

async function showStatus(label) {
  const result = await client.query(`
    SELECT conname, convalidated
    FROM pg_constraint
    WHERE conrelid = 'pg_temp.article_links'::regclass
      AND conname = 'article_links_article_fk'
      AND contype = 'f'
  `);
  console.log(label, result.rows);
}

try {
  await client.connect();
  console.log((await client.query('SHOW server_version')).rows);
  await client.query(`CREATE TEMP TABLE articles (
    article_id integer PRIMARY KEY,
    title text NOT NULL
  )`);
  await client.query(`CREATE TEMP TABLE article_links (
    link_id integer PRIMARY KEY,
    article_id integer,
    label text NOT NULL
  )`);
  await client.query(
    'INSERT INTO pg_temp.articles VALUES ($1, $2)',
    [1, 'Desk arrangement']
  );
  await client.query(`INSERT INTO pg_temp.article_links VALUES
    (10, 1, 'Matching article'),
    (11, 99, 'Older unmatched reference')`);
  await client.query(`ALTER TABLE pg_temp.article_links
    ADD CONSTRAINT article_links_article_fk
    FOREIGN KEY (article_id)
    REFERENCES pg_temp.articles (article_id) NOT VALID`);
  await showStatus('After adding');

  await expectForeignKeyError(
    'New unmatched reference',
    'INSERT INTO pg_temp.article_links VALUES ($1, $2, $3)',
    [12, 99, 'New reference']
  );
  await expectForeignKeyError(
    'Changed unmatched reference',
    'UPDATE pg_temp.article_links SET article_id = $1 WHERE link_id = $2',
    [98, 11]
  );
  await client.query(
    'UPDATE pg_temp.article_links SET label = $1 WHERE link_id = $2',
    ['Revised label', 11]
  );
  console.log('Label-only update succeeded');

  const unmatched = await client.query(`
    SELECT l.link_id, l.article_id, l.label
    FROM pg_temp.article_links AS l
    WHERE l.article_id IS NOT NULL
      AND NOT EXISTS (
        SELECT 1 FROM pg_temp.articles AS a
        WHERE a.article_id = l.article_id
      )
    ORDER BY l.link_id
  `);
  console.log('Unmatched references', unmatched.rows);
  await expectForeignKeyError('Validation before repair', validate);
  await showStatus('After failed validation');

  await client.query(
    'UPDATE pg_temp.article_links SET article_id = $1 WHERE link_id = $2',
    [1, 11]
  );
  await client.query(validate);
  await showStatus('After successful validation');
} finally {
  await client.end();
}

Führen Sie das gespeicherte Programm im selben Projektverzeichnis aus:

node not-valid-demo.mjs

Das JavaScript hat während der Vorbereitung eine Node-Syntaxprüfung bestanden. In der Rechercheumgebung war keine Datenbankausführung möglich. Die folgenden Ergebnisse entsprechen daher dem dokumentierten Verhalten und stammen nicht aus einem aufgezeichneten Ausführungsprotokoll.

Wie sollten Sie die Ergebnisse interpretieren?

Zunächst sollte die Einschränkung mit convalidated auf false angezeigt werden. Im Katalog ist dieses Feld dafür vorgesehen anzugeben, ob die Einschränkung validiert wurde. Die Abfrage filtert sowohl nach Tabellen- als auch nach Einschränkungsnamen, da Einschränkungsnamen in der Datenbank nicht unbedingt eindeutig sind. PostgreSQL: pg_constraint

Anschließend sollten sowohl das neue Einfügen einer nicht passenden Referenz als auch die Änderung einer nicht passenden Referenz jeweils 23503 ergeben. PostgreSQL bezeichnet diesen SQLSTATE als foreign_key_violation und empfiehlt, Fehlercodes statt lokalisierter Meldungstexte zu prüfen. Die Hilfsfunktion akzeptiert nur den erwarteten Code und die erwartete Einschränkung; unerwartete Fehler beenden das Programm. PostgreSQL-Fehlercodes

Bei der Aktualisierung nur der Beschriftung verweist Verknüpfung 11 weiterhin auf Artikel 99. Die Diagnoseabfrage sollte daher genau diese Verknüpfung zurückgeben, jetzt mit der Beschriftung Revised label. Die explizite Bedingung IS NOT NULL schließt optionale Nullreferenzen aus, während NOT EXISTS nicht-null-IDs identifiziert, die in articles fehlen. Dies sind beispielhafte Ergebnisse, die sich aus den bereitgestellten synthetischen Zeilen ergeben.

Die Validierung sollte zu diesem Zeitpunkt fehlschlagen. Nachdem die Korrektur Verknüpfung 11 so geändert hat, dass sie auf Artikel 1 verweist, sollte die Validierung erfolgreich sein und convalidated den Wert true annehmen. VALIDATE CONSTRAINT prüft die zuvor nicht validierte Einschränkung durch einen Tabellenscan; die Funktion nimmt selbst keine Korrekturen vor. PostgreSQL: ALTER TABLE

Die Korrektur ist für diese Demonstration eine bewusste redaktionelle Entscheidung: Beide Verknüpfungen sollen auf Desk arrangement verweisen. Prüfen Sie bei Ihren eigenen Artikeldaten jede nicht passende Referenz, bevor Sie entscheiden, ob Sie die ID korrigieren, den fehlenden Artikel ergänzen oder eine veraltete Verknüpfung entfernen. Alle nicht passenden IDs automatisch auf einen einzigen Artikel umzuleiten, würde Unterschiede verschleiern, die die Datenbank nicht auflösen kann.

Wie wenden Sie diese Abfolge auf befüllte Tabellen an?

Gehen Sie in drei getrennten Schritten vor: Fügen Sie die Einschränkung mit NOT VALID hinzu und übernehmen Sie sie, prüfen und korrigieren Sie nicht passende Referenzen und führen Sie dann die Validierung aus. Beim Hinzufügen des Fremdschlüssels wird auf beiden Tabellen eine SHARE ROW EXCLUSIVE-Sperre gesetzt. Die Validierung setzt auf der referenzierenden Tabelle eine SHARE UPDATE EXCLUSIVE-Sperre und auf der referenzierten Tabelle eine ROW SHARE-Sperre. Während des Validierungsscans sind dadurch gewöhnliche gleichzeitige Aktualisierungen möglich. Das Anfordern von Sperren kann dennoch zu Wartezeiten führen. PostgreSQL: ALTER TABLE

Wenn Sie das anfängliche Hinzufügen und die spätere Validierung in getrennten Transaktionen ausführen, bleibt der Zweck des gestuften Vorgehens erhalten: Die erste Transaktion kann abgeschlossen werden, bevor der längere Scan beginnt. Planen Sie das Hinzufügen als kurzen Vorgang und nutzen Sie die Diagnoseabfrage als Hilfsmittel zur Bereinigung; die erfolgreiche Validierung ist der abschließende Schritt.

Die Demonstration verzichtet bewusst auf einen expliziten Transaktionsblock, damit erwartete Fehler einzelner Anweisungen separat behandelt werden können. Wenn Sie die Abfolge auf node-postgres-Transaktionen übertragen, rufen Sie BEGIN, COMMIT und ROLLBACK selbst auf und führen Sie alle Anweisungen einer Transaktion auf demselben Client aus. Die node-postgres-Dokumentation rät ausdrücklich davon ab, pool.query() für Transaktionsanweisungen zu verwenden. node-postgres: Transaktionen

Berücksichtigen Sie die drei Beobachtungen aus dem Beispiel gemeinsam: Nicht passende Zeilen zeigen den erforderlichen Korrekturaufwand, abgewiesene neue Referenzen belegen die fortlaufende Durchsetzung der Einschränkung und convalidated = true dokumentiert die erfolgreiche Validierung. Damit haben Sie einen konkreten Abschluss für das Hinzufügen der Beziehung zu befüllten Artikeltabellen.

Quellen