PostgreSQL: Wahl von ON DELETE RESTRICT, CASCADE oder SET NULL ohne Verlust verknüpfter Datensätze

Rahmen Sie die Löschrichtlinie als Entscheidung zur Datenaufbewahrung

Jeder Fremdschlüssel enthält eine implizite Antwort auf eine geschäftliche Frage: Was soll mit den Zeilen geschehen, die auf einen referenzierten Datensatz zeigen, wenn dieser verschwindet? ON DELETE RESTRICT antwortet, dass die Abhängigkeiten überleben müssen und der Eltern-Datensatz nicht entfernt werden kann, bis diese Abhängigkeiten explizit behandelt wurden. ON DELETE CASCADE antwortet, dass die Abhängigkeiten untrennbare Teile des Eltern-Datensatzes sind und gemeinsam verschwinden sollten. ON DELETE SET NULL antwortet, dass die Referenz optional ist und die abhängige Zeile weiterhin ohne sie existieren kann. Dies sind keine austauschbaren Syntaxoptionen; sie kodieren Aufbewahrungsregeln, die beeinflussen, welche Daten nach einem Löschvorgang noch abfragbar sind.

Die PostgreSQL-Dokumentation besagt, dass die Aktionen intuitive Entscheidungen widerspiegeln: Das Löschen eines referenzierten Produkts verbieten, die Bestellungen ebenfalls löschen oder etwas anderes tun. Die falsche Wahl kann stillschweigend Zeilen entfernen, die eine andere Beziehung zur Bewahrung benötigt hätte, oder legitime Bereinigungsarbeiten blockieren, weil Abhängigkeiten nie zum Überleben vorgesehen waren.

Diese Entscheidung sollte der Bedeutung der Beziehung folgen, nicht der Bequemlichkeit des Schemaautors. Ein Produktkatalogeintrag, der von historischen Bestellungen referenziert wird, ist nicht dasselbe wie eine Bestellposition, die nur existiert, weil die Bestellung existiert.

CREATE TABLE products (product_no integer PRIMARY KEY, name text, price numeric);
CREATE TABLE orders (order_id integer PRIMARY KEY, shipping_address text);
CREATE TABLE order_items (
  product_no integer REFERENCES products ON DELETE RESTRICT,
  order_id integer REFERENCES orders ON DELETE CASCADE,
  quantity integer,
  PRIMARY KEY (product_no, order_id)
);

Lesen Sie das Standardverhalten von NO ACTION, bevor Sie es ändern

Wenn Sie einen Fremdschlüssel ohne ON DELETE-Klausel schreiben, wendet PostgreSQL ON DELETE NO ACTION an. Die Dokumentation erklärt, dass dies bedeutet, dass der Löschvorgang in der referenzierten Tabelle fortgesetzt werden darf, aber die Fremdschlüsseleinschränkung weiterhin erfüllt sein muss, sodass die Operation normalerweise zu einem Fehler führt. Der wesentliche Unterschied zu RESTRICT liegt im Timing und in der Aufschubbarkeit. NO ACTION prüft die Einschränkung am Ende der Anweisung oder am Ende der Transaktion, wenn die Einschränkung aufschubbar ist, was anderen Befehlen die Möglichkeit gibt, die Situation zu korrigieren, bevor die Prüfung ausgelöst wird. RESTRICT prüft sofort und kann nicht aufgeschoben werden.

Das Verlassen auf die implizite Standardeinstellung ist fragil, da ein Leser des Schemas nicht erkennen kann, ob der Autor eine Richtlinie beabsichtigt hat oder einfach vergessen hat, eine anzugeben. Das explizite Angeben der Aktion, selbst wenn es NO ACTION ist, dokumentiert die Entscheidung und beseitigt Mehrdeutigkeiten während Code-Reviews oder Migrationen.

-- These two are NOT equivalent:
-- product_no integer REFERENCES products  -- defaults to NO ACTION, deferrable
-- product_no integer REFERENCES products ON DELETE RESTRICT  -- immediate, non-deferrable

Wählen Sie RESTRICT, wenn referenzierte Zeilen überleben müssen

Verwenden Sie ON DELETE RESTRICT, wenn das Löschen eines Eltern-Datensatzes, während abhängige Zeilen existieren, sofort fehlschlagen soll. Dies zwingt die Anwendung oder den Operator, eine explizite Entscheidung über die Abhängigkeiten zu treffen, bevor der Eltern-Datensatz entfernt werden kann. Die PostgreSQL-Dokumentation illustriert dies mit dem Beispiel Produkte und Bestellpositionen: Produkte und Bestellungen sind verschiedene Dinge, daher könnte es als problematisch angesehen werden, wenn das automatische Löschen eines Produkts auch das Löschen einiger Bestellpositionen verursachen würde.

RESTRICT ist die richtige Standardeinstellung für Stammdaten, Referenztabellen und jede Entität, deren Entfernung nachgelagerte Konsequenzen hat, die eine menschliche Überprüfung erfordern. Die Fehlermeldung von RESTRICT ist deterministisch und sofortig, was es leicht macht, sie im Anwendungscode zu erfassen und dem Benutzer eine aussagekräftige Meldung anzuzeigen.

-- Attempting this with RESTRICT on product_no will raise an error:
DELETE FROM products WHERE product_no = 7;
-- ERROR: update or delete on table "products" violates
--        foreign key constraint on table "order_items"

Wählen Sie CASCADE nur für zeilenabhängige Lifecycle-Datensätze

ON DELETE CASCADE ist angemessen, wenn Kinderzeilen nur als Teil des Lebenszyklus des Eltern-Datensatzes existieren und keine eigenständige Bedeutung haben. Die Dokumentation merkt an, dass Bestellpositionen Teil einer Bestellung sind und es praktisch ist, wenn sie automatisch gelöscht werden, wenn eine Bestellung gelöscht wird. Die Kaskade wandert vom referenzierten Datensatz zu jeder referenzierenden Zeile in einem einzigen Vorgang.

Die Gefahr besteht darin, dass CASCADE Daten entfernen kann, die eine andere Beziehung zur Bewahrung benötigt hätte. Wenn eine order_item-Zeile auch durch ein Versandmanifest oder ein Audit-Log referenziert wird, wird das Kaskadieren des Löschens von Bestellungen die order_item-Zeile entfernen und dann entlang dieser anderen Fremdschlüssel weitere Einschränkungen verletzen oder kaskadieren. Verwenden Sie CASCADE nur, wenn Sie jede nachgelagerte Referenz verfolgt und bestätigt haben, dass die automatische Entfernung für alle davon die beabsichtigte Verhaltensweise ist.

-- Deleting an order cascades to its line items:
DELETE FROM orders WHERE order_id = 42;
-- All rows in order_items with order_id = 42 are removed automatically.

Wählen Sie SET NULL nur für optionale Referenzen

ON DELETE SET NULL behält die referenzierende Zeile bei, setzt aber die Fremdschlüsselspalte auf NULL. Dies ist angemessen, wenn die Beziehung optionale Informationen darstellt. Die Dokumentation gibt das Beispiel einer Produktmanager-Referenz: Wenn der Produktmanagereintrag gelöscht wird, kann es nützlich sein, den Produktmanager des Produkts auf null zu setzen.

SET NULL erfordert, dass die referenzierenden Spalten NULL-Werte akzeptieren. Es kann nicht auf einer NOT NULL-Spalte oder auf einer Spalte verwendet werden, die Teil eines Primärschlüssels ist, ohne sorgfältige Planung. Für zusammengesetzte Fremdschlüssel ermöglicht das Spaltenlistenformat, nur eine Teilmenge der referenzierenden Spalten auf NULL zu setzen, während andere intakt bleiben, was notwendig ist, wenn ein Teil des zusammengesetzten Schlüssels gefüllt bleiben muss.

-- Column-list form for composite foreign keys:
CREATE TABLE posts (
  tenant_id integer REFERENCES tenants ON DELETE CASCADE,
  post_id integer NOT NULL,
  author_id integer,
  PRIMARY KEY (tenant_id, post_id),
  FOREIGN KEY (tenant_id, author_id) REFERENCES users
    ON DELETE SET NULL (author_id)
);
-- Without the column list, tenant_id would also be set to NULL,
-- violating the primary key.

Vergleichen Sie die drei Aktionen in einem kleinen Schema

Betrachten Sie das Schema products, orders und order_items aus der Dokumentation. Die Tabelle order_items hat zwei Fremdschlüssel mit unterschiedlichen Aktionen: product_no verwendet RESTRICT und order_id verwendet CASCADE. Wenn Sie DELETE FROM products WHERE product_no = 7 versuchen und dieses Produkt in einer beliebigen order_items-Zeile erscheint, schlägt die Anweisung sofort mit einem Fremdschlüsselverletzungsfehler fehl. Keine Zeilen werden aus einer der beiden Tabellen entfernt.

Wenn Sie DELETE FROM orders WHERE order_id = 42 versuchen, entfernt die CASCADE-Aktion jede order_items-Zeile, die auf diese Bestellung verweist, und der Löschvorgang gelingt. Dasselbe Schema demonstriert sowohl schützendes als auch automatisches Verhalten, abhängig davon, welchen Eltern-Datensatz Sie anvisieren.

SET NULL würde angewendet, wenn beispielsweise orders eine optionale assigned_shipper-Spalte hätte, die auf eine shipper-Tabelle verweist. Das Löschen eines Versenders würde die Bestellung intakt lassen, wobei assigned_shipper auf NULL gesetzt wird, wodurch die Bestellhistorie bewahrt wird, während die nun ungültige Referenz entfernt wird.

-- Outcome table for the example schema:
-- DELETE FROM products WHERE product_no = 7
--   -> ERROR (RESTRICT blocks it, dependents survive)
-- DELETE FROM orders WHERE order_id = 42
--   -> SUCCESS (CASCADE removes matching order_items)
-- DELETE FROM shippers WHERE shipper_id = 3
--   -> SUCCESS (SET NULL clears orders.assigned_shipper)

Überprüfen Sie die Richtlinie, bevor Sie sie auf echte Daten anwenden

Bevor Sie eine Produktionsbeschränkung ändern, untersuchen Sie die aktuelle Aktion mit einer Abfrage gegen pg_constraint oder durch Lesen des Schemas mit \d in psql. Identifizieren Sie, wie viele abhängige Zeilen für die Eltern-Datensätze existieren, die Sie löschen möchten. Die Dokumentation warnt, dass das Schreiben von DELETE FROM products ohne WHERE-Klausel alle Zeilen entfernt, daher sollten Sie Ihre Testlöschungen immer einschränken.

Führen Sie die beabsichtigte DELETE-Anweisung innerhalb einer Transaktion aus, die Sie zurückrollen. Dies ermöglicht es Ihnen zu beobachten, ob RESTRICT einen Fehler auslöst, ob CASCADE die erwartete Anzahl von Kindzeilen entfernt oder ob SET NULL Zeilen mit NULL-Referenzen hinterlässt, alles ohne Änderungen zu übernehmen. Erst nach Bestätigung des Verhaltens sollten Sie ein ALTER TABLE ausgeben, um die Beschränkungsaktion in der Produktion zu ändern.

BEGIN;
DELETE FROM orders WHERE order_id = 42;
SELECT count(*) FROM order_items WHERE order_id = 42;
-- Expect 0 if CASCADE is correct.
ROLLBACK;
-- No data was permanently removed.

Wissen, wann die Richtlinie unzureichend ist

Diese Aktionen regeln, was mit referenzierenden Zeilen geschieht, wenn ein referenzierter Datensatz gelöscht wird. Sie behandeln keine beliebige Bereinigung, Datenaufbewahrungspläne oder die Entfernung von Zeilen, die nicht durch einen Fremdschlüssel verbunden sind. Eine CASCADE kann ein inkorrektes Beziehungsmodell nicht kompensieren; wenn die Kindzeilen ursprünglich nicht mit dem Eltern-Datensatz verknüpft hätten werden sollen, wird keine Löschaktion das zugrunde liegende Schema-Design beheben.

Die Dokumentation weist auch darauf hin, dass ON UPDATE verwandtes, aber nicht identisches Verhalten hat, und dass Spaltenlisten für SET NULL und SET DEFAULT unter ON UPDATE nicht angegeben werden können. Eine Richtlinie, die für Löschungen funktioniert, überträgt sich möglicherweise nicht direkt auf Aktualisierungen. Schließlich kann eine unbegrenzte DELETE-Anweisung kombiniert mit CASCADE große Mengen an Daten in einer einzigen Transaktion entfernen, daher bleiben anwendungsspezifische Schutzmaßnahmen und explizite WHERE-Klauseln unabhängig von der Beschränkungsaktion notwendig.

-- This single statement could cascade through many tables:
DELETE FROM products;  -- no WHERE clause, caveat programmer
-- If CASCADE is set on multiple levels, this can be destructive.