PostgreSQL-Fremdschlüssel-Löschrichtlinie wählen, ohne die falschen Zeilen zu verlieren

Entscheiden Sie, ob das Kind eigenständig existieren kann

Eine Detailzeile, die ohne ihren Elterndatensatz keine Bedeutung hat, kann einer kaskadierenden Löschung entsprechen. Ein historischer Datensatz, der erhalten bleiben muss, kann das Verhindern des Löschens oder das Beibehalten der Zeile mit einer optionalen Beziehung erfordern. Beginnen Sie mit dieser geschäftlichen Entscheidung, statt die Aktion zu wählen, die einen fehlgeschlagenen DELETE zum Erfolg führt.

Ein Fremdschlüssel schützt die in seiner Bedingung deklarierte Beziehung. Er erzwingt nicht von sich aus eine Beibehaltungsrichtlinie, gewährt keine Genehmigung zum Entfernen personenbezogener Daten und erhält keine externen Dateien, die zum Datensatz gehören.

Prüfen Sie die tatsächlich geltende Bedingung

Finden Sie die referenzierte Tabelle, die referenzierenden Spalten und die konfigurierte ON DELETE-Aktion im Datenbankschema oder im Verwaltungstool. Schließen Sie die Aktion nicht aus dem Namen einer Spalte oder aus einem ORM-Modell, das nicht mit der bereitgestellten Datenbank übereinstimmen muss.

CASCADE und SET NULL gehören nach der REFERENCES-Klausel in der Fremdschlüsseldefinition. Ein Befehl wie DELETE FROM parent WHERE id = 1 CASCADE ist keine PostgreSQL-DELETE-Syntax zur Wahl einer Fremdschlüsselaktion.

Vergleichen Sie die drei Aktionen

Bei RESTRICT wird ein Versuch, einen Elterndatensatz zu entfernen, der noch von einem Kind referenziert wird, abgelehnt. Bei CASCADE entfernt das Löschen des Elterndatensatzes die referenzierenden Kinder. Bei SET NULL bleiben die Kinder erhalten, aber ihre referenzierenden Spalten werden null. Weitere Bedingungen gelten weiterhin und können den Vorgang verhindern.

SET NULL ist nur geeignet, wenn die betroffenen Spalten und die Anwendungslogik null akzeptieren. Eine NOT NULL-Bedingung kann das Löschen fehlschlagen lassen. NO ACTION ist eine weitere Richtlinie, sollte aber nicht in jeder Situation als identisch mit RESTRICT beschrieben werden: Die verzögerte Prüfung von Bedingungen kann die Unterscheidung wichtig machen.

Verfolgen Sie ein isoliertes Beispiel

Dieses anschauliche Skript verwendet drei verschiedene Kindtabellen und drei verschiedene Elternzeilen, um die Richtlinien unabhängig zu halten. Es hängt nicht drei widersprüchliche Richtlinien an eine Beziehung. Führen Sie solche Demonstrationen nur in einer verworfenen Sitzung aus; die Transaktion endet mit ROLLBACK und behauptet nicht, Ihr Produktionsschema zu prüfen.

BEGIN;
CREATE TEMP TABLE demo_parent (id integer PRIMARY KEY);
CREATE TEMP TABLE demo_restrict (
    id integer PRIMARY KEY,
    parent_id integer REFERENCES demo_parent(id) ON DELETE RESTRICT
);
CREATE TEMP TABLE demo_cascade (
    id integer PRIMARY KEY,
    parent_id integer REFERENCES demo_parent(id) ON DELETE CASCADE
);
CREATE TEMP TABLE demo_null (
    id integer PRIMARY KEY,
    parent_id integer REFERENCES demo_parent(id) ON DELETE SET NULL
);
INSERT INTO demo_parent VALUES (1), (2), (3);
INSERT INTO demo_restrict VALUES (10, 1);
INSERT INTO demo_cascade VALUES (20, 2);
INSERT INTO demo_null VALUES (30, 3);
DELETE FROM demo_parent WHERE id IN (2, 3);
SELECT count(*) AS remaining_cascade_rows FROM demo_cascade;
SELECT id, parent_id FROM demo_null ORDER BY id;
ROLLBACK;

Verstehen Sie das erwartete Ergebnis und den abgelehnten Fall

Die erwartete Kaskadenanzahl ist null, weil das mit Elterndatensatz 2 verknüpfte Kind entfernt wird. Die Zeile mit id 30 bleibt in demo_null mit parent_id gleich null. Das Skript löscht Elterndatensatz 1 nicht, sodass dessen Restrict-Kind verbleibt. Diese Ergebnisse folgen aus den genannten Bedingungen und Beispieldaten; sie sind anschaulich, keine Messungen.

Ein Versuch, vor dem Rollback Elterndatensatz 1 zu löschen, würde abgelehnt, weil demo_restrict ihn noch referenziert. Diese absichtlich fehlschlagende Anweisung wird vom Skript ausgelassen: Ein Fehler kann die Transaktion abbrechen, und nachfolgender SQL benötigt eine entsprechende Rollback-Behandlung.

Prüfen Sie den Umfang vor einer echten Löschung

Zählen Sie die Zeilen, die auf die vorgeschlagene Elternprädikatsbedingung passen, und prüfen Sie abhängige Tabellen. Kaskaden können über weitere Beziehungen hinweg fortgesetzt werden und mehr Zeilen entfernen, als eine einzelne Kindtabellenanzahl nahelegt. Prüfen Sie die vollständige Abhängigkeitskette und die anwendungstechnischen Folgen.

Eine Vorschauabfrage und ein späterer DELETE sind nicht automatisch eine geschützte atomare Entscheidung bei gleichzeitigen Änderungen. Planen Sie das erforderliche Transaktions- und Sperrverhalten für den echten Vorgang, statt anzunehmen, dass eine frühere Zählung die Daten einfriert.

Prüfen Sie die Leistung, ohne eine garantierte Geschwindigkeit zu behaupten

Das Löschen eines referenzierten Datensatzes kann das Finden von Zeilen auf der referenzierenden Seite erfordern. PostgreSQL erstellt nicht automatisch einen Index auf den referenzierenden Fremdschlüsselspalten, nur weil Sie den Fremdschlüssel deklarieren. Prüfen Sie vorhandene Indizes und die Arbeitslast, bevor Sie entscheiden, ob ein weiterer Index geeignet ist.

Große Kaskaden können Sperren halten, erhebliche Datenbankarbeit erzeugen und gleichzeitige Benutzer beeinflussen. Verwenden Sie ein geeignetes Wartungsverfahren und einen Wiederherstellungsplan. Das kleine Beispiel mit temporären Tabellen stellt das Verhalten dar, nicht die Kosten einer Produktionslöschung.

Halten Sie relationale Aktionen von der Anwendungsaufräumung getrennt

Datenbankbedingungen wirken auf die deklarierten Datenbankbeziehungen. Sie garantieren nicht die Aufräumung von Dateien, Remote-Diensten oder Ereignissen, die außerhalb der Transaktion verwaltet werden. Berücksichtigen Sie diese Nebeneffekte explizit.

Ein Rollback schützt im Beispiel transaktionsbezogene Datenbankänderungen, nicht beliebige Aktionen, die von externen Systemen ausgeführt werden. Wenn eine bereitgestellte Richtlinie falsch ist, behandeln Sie ihre Änderung als überprüfte Schemaanpassung, nicht als schnellen Ausweg für eine einzelne fehlschlagende Anfrage.