Kombinieren Sie ON DELETE SET NULL und ON DELETE CASCADE, indem Sie jeder Aktion einen separaten Fremdschlüssel zuweisen. Diese Anleitung verwendet synthetische Artikel, um zu zeigen, wie beim Löschen einer Sammlung die zugehörigen Artikel erhalten bleiben können, während beim Löschen eines Artikels dessen Abschnitte entfernt werden.

Die Aufgabe besteht darin, dieses Verhalten festzulegen, beide Löschpfade im Terminal eines Mac zu überprüfen und die Prüfungen mit node-postgres zu wiederholen. Die Beispiele verwenden gewöhnliche Fremdschlüssel und temporäre Tabellen, sodass jeder Durchlauf eigene Beispieldatensätze hat.

Welche Beziehung sollte welche Löschaktion verwenden?

Eine Löschaktion gehört zum Fremdschlüssel in der referenzierenden Tabelle. SET NULL löscht die Referenz, wenn die referenzierte Zeile verschwindet; CASCADE löscht die referenzierende Zeile. PostgreSQL nennt optionale Beziehungen als geeigneten Anwendungsfall für SET NULL und abhängige Komponenten als geeigneten Anwendungsfall für CASCADE. Constraints

In diesem Beispiel gruppiert eine Sammlung Artikel, aber ein Artikel kann auch ohne Sammlung existieren. Ein Abschnitt gehört zu genau einem Artikel und hat keine eigenständige Funktion. Dies sind ausdrückliche Designentscheidungen für das Beispiel:

Fremdschlüssel Gewählte Aktion Beabsichtigtes Ergebnis
demo_articles.collection_id referenziert demo_collections.collection_id ON DELETE SET NULL Beim Entfernen einer Sammlung bleiben ihre Artikel ohne Sammlungsreferenz erhalten.
demo_sections.article_id referenziert demo_articles.article_id ON DELETE CASCADE Beim Entfernen eines Artikels werden dessen Abschnitte entfernt.

Das resultierende Verhalten hängt davon ab, wo der Löschvorgang beginnt. Das Löschen einer Sammlung ändert collection_id eines Artikels; es löscht den Artikel nicht. Daher gibt es für ON DELETE CASCADE des Abschnitts-Fremdschlüssels keine Artikel-Löschung, auf die reagiert werden könnte. Wird der Artikel selbst gelöscht, wird diese Kaskade ausgelöst. Das ergibt sich aus den dokumentierten Definitionen der beiden Aktionen. CREATE TABLE

Verwenden Sie nicht zwei konkurrierende Fremdschlüssel für dieselbe Beziehung, um „Referenz aufheben und Zeile löschen“ auszudrücken. Entscheiden Sie, ob die referenzierende Zeile bestehen bleiben soll, und wählen Sie dann die Aktion, die diese Entscheidung umsetzt.

Ein vollständiges Beispiel auf Ihrem Mac erstellen

Sie benötigen einen laufenden PostgreSQL-Server, eine vorhandene Datenbank, zu der Sie eine Verbindung herstellen können, und psql im Terminal. Die folgenden Befehle verwenden die vorhandene lokale Datenbank mit dem Namen postgres; ersetzen Sie diesen Namen bei Bedarf durch den Namen Ihrer vorhandenen Datenbank. Installation und Servereinrichtung sind nicht Teil dieser Anleitung.

Speichern Sie den folgenden Inhalt als Klartextdatei namens setup.sql in einem Arbeitsordner. Alle drei Tabellen sind temporär: PostgreSQL entfernt temporäre Tabellen, wenn ihre Sitzung endet. Auch jede referenzierte Tabelle in diesem Beispiel ist temporär, da PostgreSQL Fremdschlüssel zwischen temporären und permanenten Tabellen nicht zulässt. CREATE TABLE

CREATE TEMP TABLE demo_collections (
    collection_id integer PRIMARY KEY,
    title text NOT NULL
);

CREATE TEMP TABLE demo_articles (
    article_id integer PRIMARY KEY,
    collection_id integer,
    title text NOT NULL,
    CONSTRAINT demo_articles_collection_fk
        FOREIGN KEY (collection_id)
        REFERENCES demo_collections (collection_id)
        ON DELETE SET NULL
);

CREATE TEMP TABLE demo_sections (
    section_id integer PRIMARY KEY,
    article_id integer NOT NULL,
    heading text NOT NULL,
    CONSTRAINT demo_sections_article_fk
        FOREIGN KEY (article_id)
        REFERENCES demo_articles (article_id)
        ON DELETE CASCADE
);

CREATE INDEX demo_articles_collection_idx
    ON demo_articles (collection_id);
CREATE INDEX demo_sections_article_idx
    ON demo_sections (article_id);

INSERT INTO demo_collections (collection_id, title) VALUES
    (1, 'Layout Notes'),
    (2, 'Color Notes');

INSERT INTO demo_articles (article_id, collection_id, title) VALUES
    (101, 1, 'Grid Spacing'),
    (102, 2, 'Color Pairings');

INSERT INTO demo_sections (section_id, article_id, heading) VALUES
    (1001, 101, 'Overview'),
    (1002, 101, 'Examples'),
    (1003, 102, 'Overview');

Beachten Sie, dass collection_id NULL-Werte zulässt, während article_id in der Abschnittstabelle erforderlich ist. Eine SET NULL-Aktion muss weiterhin die übrigen Einschränkungen der Tabelle erfüllen. Die expliziten Indizes decken die referenzierenden Spalten ab; PostgreSQL erstellt diese Indizes nicht automatisch, wenn ein Fremdschlüssel deklariert wird. Constraints

Beide Löschpfade in einer Sitzung überprüfen

Speichern Sie den nächsten Block als verify.sql neben setup.sql. Es handelt sich um ein vollständiges psql-Skript: \ir bindet die Einrichtungsdatei relativ zum Prüfskript ein. Führen Sie beide Dateien über dieses Skript aus, damit die temporären Tabellen in derselben Sitzung bleiben. Die Option -f führt eine Datei aus, und ON_ERROR_STOP beendet die Skriptverarbeitung nach einem Fehler. psql

BEGIN;
\ir setup.sql

DELETE FROM demo_collections
WHERE collection_id = 1
RETURNING collection_id;

SELECT a.article_id,
       a.title,
       a.collection_id,
       c.title AS collection_title,
       count(s.section_id) AS section_count
FROM demo_articles AS a
LEFT JOIN demo_collections AS c
    ON c.collection_id = a.collection_id
LEFT JOIN demo_sections AS s
    ON s.article_id = a.article_id
GROUP BY a.article_id, a.title, a.collection_id, c.title
ORDER BY a.article_id;

DELETE FROM demo_articles
WHERE article_id = 101
RETURNING article_id;

SELECT section_id, article_id, heading
FROM demo_sections
ORDER BY section_id;

ROLLBACK;

Wechseln Sie im Terminal in den Ordner mit den Dateien und führen Sie Folgendes aus:

psql -X -v ON_ERROR_STOP=1 -d postgres -f verify.sql

Die erwarteten Ergebnisse der ersten Abfrage ergeben sich aus den Beispieldatensätzen:

Artikel Sammlungs-ID nach dem Löschen der Sammlung Sammlungstitel Anzahl der Abschnitte
101 NULL NULL 2
102 2 Color Notes 1

Artikel 101 bleibt mit seinen zwei Abschnitten bestehen. Nach dem zweiten Löschvorgang sollte die Abfrage der Abschnitte nur noch Abschnitt 1003 zurückgeben, der zu Artikel 102 gehört. Diese beiden Prüfungen unterscheiden zwischen dem Erhalt eines Artikels und dem Löschen eines Artikels samt seinen abhängigen Zeilen.

Die Prüfungsabfrage verwendet bewusst LEFT JOIN. PostgreSQL behält eine nicht zugeordnete Zeile aus der linken Tabelle bei und liefert für die fehlenden Werte auf der rechten Seite NULL-Werte. Ein innerer Join mit Sammlungen würde Artikel 101 auslassen, nachdem dessen Referenz NULL geworden ist. Dadurch würde ein erhaltener Artikel in den Abfrageergebnissen verschwinden. Table Expressions

Die RETURNING-Klauseln melden die aus den Zieltabelle gelöschten Zeilen. Sie liefern kein Gesamtverzeichnis aller durch die Kaskade entfernten Abschnitte; die separate Abfrage der Abschnitte überprüft dieses Ergebnis. DELETE

Das abschließende ROLLBACK macht die Änderungen der Demonstration rückgängig. PostgreSQL behandelt auch eine eigenständige Anweisung als Transaktion. Eine explizite Transaktion ist daher hilfreich, um Einrichtung, Löschung, Prüfung und Bereinigung zusammenzufassen. Transactions

Dieselben Prüfungen mit node-postgres ausführen

Lassen Sie für die JavaScript-Version setup.sql unverändert. Verwenden Sie für dieses Beispiel Node.js 24 und installieren Sie das dokumentierte Paket pg im Arbeitsordner: node-postgres

npm install pg

Speichern Sie das folgende vollständige Programm als verify.mjs neben setup.sql. Es liest dieselben synthetischen Testdaten, überprüft, dass beim Löschen der Sammlung beide Artikel und alle drei Abschnitte erhalten bleiben, und prüft anschließend, dass beim Löschen eines Artikels nur die zugehörigen Abschnitte entfernt werden.

Das Programm verwendet während der gesamten Transaktion denselben verbundenen Client, wie von node-postgres gefordert. Die Abfragewerte werden über die dokumentierte Parameter-Array-Schnittstelle mit $1 übergeben. Transactions, Queries

import pg from 'pg';
import { readFile } from 'node:fs/promises';
import assert from 'node:assert/strict';

const { Client } = pg;
const client = new Client();
const setup = await readFile(
  new URL('./setup.sql', import.meta.url),
  'utf8'
);

await client.connect();
try {
  await client.query('BEGIN');
  await client.query(setup);

  const removed = await client.query(
    'DELETE FROM demo_collections WHERE collection_id = $1 RETURNING collection_id',
    [1]
  );
  assert.deepEqual(removed.rows, [{ collection_id: 1 }]);

  const articles = await client.query(
    'SELECT article_id, collection_id FROM demo_articles ORDER BY article_id'
  );
  assert.deepEqual(articles.rows, [
    { article_id: 101, collection_id: null },
    { article_id: 102, collection_id: 2 }
  ]);

  const before = await client.query(
    'SELECT section_id FROM demo_sections ORDER BY section_id'
  );
  assert.deepEqual(
    before.rows.map(row => row.section_id),
    [1001, 1002, 1003]
  );

  await client.query(
    'DELETE FROM demo_articles WHERE article_id = $1',
    [101]
  );
  const after = await client.query(
    'SELECT section_id FROM demo_sections ORDER BY section_id'
  );
  assert.deepEqual(after.rows, [{ section_id: 1003 }]);

  console.log('Expected SET NULL and CASCADE outcomes verified.');
  await client.query('ROLLBACK');
} catch (error) {
  try {
    await client.query('ROLLBACK');
  } catch (rollbackError) {
    console.error('Rollback failed:', rollbackError.message);
  }
  throw error;
} finally {
  await client.end();
}

Führen Sie das Programm mit derselben vorhandenen lokalen Datenbank aus:

PGDATABASE=postgres node verify.mjs

new Client() liest die PostgreSQL-Verbindungsvariablen der Umgebung. Behalten Sie alle Verbindungseinstellungen bei, die Ihr lokaler Server bereits benötigt. Allein durch Ändern des Datenbanknamens wird kein anderer Server konfiguriert. Connecting

Eine fehlgeschlagene Assertion führt zum Rollback und gibt einen Fehler aus. Die Erfolgsmeldung erscheint erst, nachdem alle Ergebnisprüfungen bestanden wurden. Das Programm führt absichtlich auch im Erfolgsfall ein Rollback aus, damit die Demonstration wiederholt ausgeführt werden kann.

Die JavaScript-Syntax wurde während der Vorbereitung geprüft. In der Rechercheumgebung war kein PostgreSQL-Programm verfügbar. Daher wurden die Datenbankbeispiele anhand der Dokumentation geprüft, statt ausgeführt zu werden; die Tabellen oben zeigen die erwarteten Ergebnisse.

Was ändert sich bei zusammengesetzten Fremdschlüsseln?

Das Hauptbeispiel verwendet Fremdschlüssel mit einer einzelnen Spalte. Ein zusammengesetzter Fremdschlüssel wirft eine weitere Frage auf: Sollen beim Löschen alle referenzierenden Spalten geleert werden oder nur die optionale Spalte? PostgreSQL 15 führte Spaltenlisten für Fremdschlüsselaktionen ON DELETE SET ein; in früheren Versionen waren alle Spalten des Fremdschlüssels betroffen. PostgreSQL 15 Release Notes

Ein zusammengesetzter Artikelverweis könnte beispielsweise eine erforderliche Editionskennung und eine optionale Kennung für einen verwandten Artikel enthalten. Mit PostgreSQL 15 oder höher kann ON DELETE SET NULL (related_article_id) die Editionskennung erhalten und zugleich den optionalen Artikelverweis leeren. Ohne diese Spaltenliste zielt SET NULL auf beide Spalten. Diese Syntax ist eine PostgreSQL-Erweiterung und für ON DELETE, aber nicht für ON UPDATE verfügbar. CREATE TABLE

Teilweises Setzen auf NULL erfordert außerdem eine kompatible Übereinstimmungsregel. Die Standardeinstellung MATCH SIMPLE erlaubt einen zusammengesetzten Fremdschlüssel mit einem NULL-Wert; MATCH FULL weist eine Mischung aus NULL- und Nicht-NULL-Referenzwerten zurück. Daher ist es mit MATCH FULL nicht vereinbar, eine erforderliche Editionskennung beizubehalten und nur die Kennung des verwandten Artikels auf NULL zu setzen. Constraints

Verwenden Sie unter PostgreSQL 14 ein Schema mit einem separaten einspaltigen optionalen Verweis wie im Hauptbeispiel, sofern dies die Beziehung korrekt abbildet. Muss die zusammengesetzte Beziehung erhalten bleiben, leeren Sie den optionalen Verweis ausdrücklich in einer Transaktion, bevor Sie die referenzierte Zeile löschen. Das ist eine von der Anwendung gesteuerte Abfolge und nicht die nicht unterstützte Syntax mit Spaltenliste.

In PostgreSQL 18 gibt es eine weitere dokumentierte Einschränkung: Temporale Fremdschlüssel mit PERIOD unterstützen weder CASCADE noch SET NULL. Wenn die Beziehung keine Abdeckung über Zeiträume umfasst, sind die hier gezeigten gewöhnlichen Fremdschlüssel der unterstützte Weg für diese Löschaktionen. CREATE TABLE

Wie diagnostizieren Sie ein unerwartetes Ergebnis?

Nutzen Sie den Löschpfad als Abfolge für die Diagnose:

  1. Prüfen Sie die referenzierende Tabelle. Untersuchen Sie \d demo_articles und \d demo_sections in der Sitzung, in der sich die Tabellen befinden. Prüfen Sie die tatsächlichen Fremdschlüsselaktionen, statt sie aus den Spaltennamen abzuleiten. PostgreSQL nennt \d als Möglichkeit, Tabellendetails und Einschränkungsnamen einzusehen. Modifying Tables
  2. Prüfen Sie NULL-Zulässigkeit und andere Einschränkungen. Schlägt das Löschen einer Sammlung fehl, vergewissern Sie sich, dass die zu leerende Spalte NULL-Werte zulässt und dass die übrigen Einschränkungen die resultierende Zeile akzeptieren. SET NULL setzt diese nicht außer Kraft. Constraints
  3. Untersuchen Sie alle relevanten Fremdschlüssel. Eine zusätzliche Beziehung kann eine andere Aktion haben. Eine ausgelassene Aktion wird standardmäßig zu NO ACTION. Dadurch kann das Löschen verhindert werden, solange Referenzen bestehen. CREATE TABLE
  4. Fragen Sie die Basistabelle ab, bevor Sie einen Join interpretieren. Wenn der Artikel dort noch existiert, untersuchen Sie Joins und Filter, die seine neue NULL-Referenz möglicherweise auslassen. Table Expressions
  5. Führen Sie Transaktionsanweisungen auf einem Client aus. Rufen Sie in einer Anwendung mit Verbindungspool einen Client für die gesamte Transaktion ab. node-postgres rät ausdrücklich davon ab, pool.query() für Transaktionsanweisungen zu verwenden. Transactions

Für dieses Artikelmodell ist der Abnahmetest konkret: Wenn Sammlung 1 entfernt wird, bleiben Artikel 101 und seine Abschnitte intakt. Wird Artikel 101 entfernt, bleibt nur der Abschnitt von Artikel 102 übrig. Führen Sie diese beiden Prüfungen gemeinsam aus, wenn Sie das Schema anpassen, damit das beabsichtigte Erhaltungs- und Löschverhalten sichtbar bleibt.

Quellen