Menu

PostgreSQL Foreign Keys und ON DELETE CASCADE

Ein Foreign Key sorgt dafür, dass eine Spalte nur Werte annimmt, die es in einer anderen Tabelle gibt. Mit REFERENCES, ON DELETE CASCADE, SET NULL und RESTRICT, einem Schlüssel für eine bestehende Tabelle und seinem Index.

Diese Seite enthält ausführbare Editoren. Bearbeiten, ausführen und Ausgabe sofort sehen.

Ein Foreign Key (Fremdschlüssel) bindet eine Spalte an den Primärschlüssel oder eine eindeutige Spalte einer anderen Tabelle. PostgreSQL lehnt jeden Wert ab, für den es dort keine passende Zeile gibt. Du schreibst ihn als column type REFERENCES other_table (column). Mit ON DELETE CASCADE oder ON DELETE SET NULL legst du fest, was mit den Kindzeilen passiert, wenn die Elternzeile gelöscht wird.

Die referenzierte Spalte braucht einen PRIMARY KEY oder UNIQUE Constraint. Bei REFERENCES customers ohne Spalte nimmt PostgreSQL den Primärschlüssel von customers.

So sieht eine Verletzung aus

Eine Bestellung für einen Kunden, den es nicht gibt, wird abgelehnt:

Der Fehler nennt den Constraint, orders_customer_id_fkey. So benennt PostgreSQL einen Foreign Key ohne eigenen Namen. Einen Kunden zu löschen, der noch Bestellungen hat, scheitert genauso, außer der Schlüssel sagt etwas anderes.

Optionen für ON DELETE

OptionWenn die Elternzeile gelöscht wird
NO ACTION (Standard)Das Löschen scheitert, solange Kindzeilen existieren
RESTRICTGenauso, aber die Prüfung lässt sich nicht ans Ende der Transaktion verschieben
CASCADEDie Kindzeilen werden mitgelöscht
SET NULLDie Foreign-Key-Spalte des Kinds wird NULL
SET DEFAULTDie Spalte des Kinds bekommt ihren Default

ON UPDATE kennt dieselben Optionen. Es greift, wenn sich der Schlüsselwert der Elternzeile ändert, was bei generierten IDs selten vorkommt.

CASCADE passt zu Zeilen, die ohne ihre Elternzeile keinen Sinn ergeben, etwa die Positionen einer Bestellung:

Nur chair bleibt übrig. Kaskaden folgen weiteren Schlüsseln, ein einziges Löschen kann also Zeilen in mehreren Tabellen entfernen.

SET NULL behält die Kindzeile und löst sie ab:

Ada bleibt, mit team_id auf NULL. Seit PostgreSQL 15 kann ON DELETE SET NULL (column) bei einem mehrspaltigen Schlüssel nur einige Spalten leeren.

Foreign Key zu einer bestehenden Tabelle hinzufügen

ALTER TABLE orders
    ADD CONSTRAINT orders_customer_id_fkey
    FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE;

Das prüft jede bestehende Zeile und scheitert, wenn eine kein Elternteil hat. Bei einer großen Tabelle fügst du den Schlüssel mit NOT VALID hinzu, dann werden nur neue Zeilen geprüft. Danach führst du ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey; aus, um die alten Zeilen zu prüfen, ohne Schreibvorgänge zu blockieren. Um die ON DELETE Aktion eines bestehenden Schlüssels zu ändern, löschst du den Constraint und legst ihn in einem einzigen ALTER TABLE neu an.

Die Foreign-Key-Spalte indizieren

PostgreSQL indiziert die referenzierte Seite, nicht die verweisende Spalte. Ohne Index auf orders.customer_id durchsucht jedes Löschen in customers die ganze Tabelle orders nach Kindzeilen, und genauso das Abrufen der Bestellungen eines Kunden:

CREATE INDEX orders_customer_id_idx ON orders (customer_id);

Um die Foreign Keys einer Tabelle aufzulisten, fragst du pg_constraint ab:

Joins über diese Schlüssel behandelt die Seite zu Joins, und das Kapitel zu Constraints im Handbuch enthält alle Regeln, auch zu verzögerbaren Schlüsseln.

Häufig gestellte Fragen

Legt PostgreSQL für einen Foreign Key einen Index an?

Nicht auf der verweisenden Spalte. Die referenzierte Spalte hat schon einen, weil sie ein Primärschlüssel oder eindeutig sein muss. Leg den Index auf der Foreign-Key-Spalte selbst an, denn Joins und Löschvorgänge in der Elterntabelle durchsuchen sie.

Wie lösche ich in PostgreSQL einen Foreign Key?

ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey;. Unbenannte Foreign Keys heißen table_column_fkey; \d orders in psql zeigt den genauen Namen.

Darf eine Foreign-Key-Spalte in PostgreSQL NULL sein?

Ja. Ein NULL in der Foreign-Key-Spalte wird nicht geprüft, die Zeile braucht dann kein Elternteil. Ergänze NOT NULL, wenn jede Zeile eines haben muss.

Kann ein Foreign Key auf dieselbe Tabelle verweisen?

Ja. Eine Spalte wie manager_id int REFERENCES employees (id) zeigt auf eine andere Zeile derselben Tabelle. So werden Bäume wie Organigramme und Kommentar-Threads gespeichert.

Illustration der Programmiersprachen bei Coddy

Lerne mit Coddy zu programmieren

Los geht's