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
| Option | Wenn die Elternzeile gelöscht wird |
|---|---|
NO ACTION (Standard) | Das Löschen scheitert, solange Kindzeilen existieren |
RESTRICT | Genauso, aber die Prüfung lässt sich nicht ans Ende der Transaktion verschieben |
CASCADE | Die Kindzeilen werden mitgelöscht |
SET NULL | Die Foreign-Key-Spalte des Kinds wird NULL |
SET DEFAULT | Die 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.