Referenzielle Integrität: ON DELETE, ON UPDATE

In Lektion 1 haben wir die referenzielle Integrität als das Versprechen kennengelernt: jeder Fremdschlüssel verweist auf eine real existierende Zeile. Soweit beim Einfügen klar. Die spannende Frage ist: was passiert wenn die referenzierte Zeile später geändert oder gelöscht wird?

Stell dir eine Tabelle kunden mit Kunde 42 vor, auf die sechs Bestellungen verweisen. Der Kunde löscht jetzt sein Konto – was passiert mit den Bestellungen? Verschwinden sie auch? Bleiben sie verwaist? Wird das Löschen verhindert? Die Antwort definierst du explizit – mit den Klauseln ON DELETE und ON UPDATE am Fremdschlüssel. SQL bietet dafür fünf Verhaltensoptionen.

1) Wo das Problem entsteht

Solange du nur INSERT-Operationen machst, prüft das DBMS einfach ob der Fremdschlüssel-Wert in der Zieltabelle existiert – fertig. Spannend wird's bei UPDATE und DELETE in der Eltern-Tabelle, also der Tabelle auf die verwiesen wird:

DELETE in der Eltern-Tabelle: Du willst Kunde 42 löschen. Aber sechs Bestellungen referenzieren ihn. Wenn du den Kunden einfach wegnimmst, würden die Bestellungen verwaisen – ihr Fremdschlüssel würde ins Leere zeigen. Das ist genau der Zustand den die referenzielle Integrität verbietet.
UPDATE des Primärschlüssels: Du willst Kunde 42 zur Kunde 99 umbenennen (selten, aber kommt vor). Wenn die Bestellungen weiterhin auf 42 zeigen, sind sie ebenfalls verwaist.

Das DBMS muss dir vorher sagen wie es in solchen Fällen reagieren soll. Genau das definierst du bei der Erstellung des Fremdschlüssels:

CREATE TABLE bestellungen (
  id BIGINT PRIMARY KEY,
  kunden_id BIGINT,
  FOREIGN KEY (kunden_id) REFERENCES kunden(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

2) Die fünf Optionen im Überblick

SQL kennt fünf Verhaltensweisen für ON DELETE und ON UPDATE. Jede hat einen klaren Anwendungsfall:

Optionen für ON DELETE / ON UPDATE

Option	Verhalten beim DELETE der referenzierten Zeile	Wann sinnvoll?
CASCADE	Die abhängigen Zeilen werden mitgelöscht.	Wenn das Kind ohne Eltern keinen Sinn hat (Bestellpositionen ohne Bestellung).
RESTRICT	Das Löschen wird abgelehnt, solange noch Kinder existieren. Fehler sofort.	Sicherheits-Default. Eltern muss explizit „leer" sein vor Löschung.
NO ACTION	Praktisch wie RESTRICT, aber die Prüfung erfolgt erst am Ende der Transaktion.	Wenn man innerhalb einer Transaktion mehrere Schritte machen will (z.B. Kinder erst umhängen, dann Eltern löschen).
SET NULL	Die FK-Spalte in den Kindern wird auf `NULL` gesetzt – Kinder bleiben, aber ohne Eltern-Bezug.	Wenn der Bezug optional ist (z.B. Kommentare bleiben sichtbar wenn Autor gelöscht ist – „Gelöschter Nutzer").
SET DEFAULT	Die FK-Spalte wird auf den definierten DEFAULT-Wert gesetzt (oft auf ein „Sammler"-Konto).	Selten genutzt. Z.B. wenn ein Mitarbeiter geht: seine Tickets gehen an Standard-Bearbeiter.

Bei ON UPDATE gilt sinngemäß dasselbe für das Ändern des Primärschlüssel-Werts. CASCADE ist hier oft sinnvoll: ändert sich die kunden_id, sollen die FKs in bestellungen mitwandern. RESTRICT ist sicherer aber unflexibler. Wichtig: nicht alle DBMS unterstützen alle Optionen vollständig – MySQL mit InnoDB unterstützt CASCADE, RESTRICT, NO ACTION, SET NULL. SET DEFAULT wird oft akzeptiert aber wie NO ACTION behandelt.

3) Live-Simulator: Lösche den Kunden

Spiel es konkret durch. Wähle eine Option, lösch dann Kunde 1 (auf den zwei Bestellungen verweisen) und beobachte das Ergebnis:

ON DELETE-Simulator

1. Wähle ein Verhalten:

2. Führe DELETE FROM kunden WHERE id = 1; aus:

Tabelle kunden

id	name
1	Anna Bauer
2	Tim Schulz
3	Lisa Wagner

Tabelle bestellungen

id	kunden_id	summe
100	1	89,90
101	2	12,50
102	1	249,00
103	3	34,90

→ Wähle eine Option und klick „DELETE ausführen"…

Beachte die Folgen: CASCADE räumt rigoros mit – elegant, aber auch gefährlich, weil ein einziges DELETE kaskadierend tausende Zeilen rausfegen kann. SET NULL erhält die Kinder als „Waisen mit unbekanntem Eltern" – nur möglich wenn die FK-Spalte NULL erlauben darf (nicht NOT NULL). RESTRICT ist der vorsichtige Default: das DBMS verweigert das Löschen und du musst dich entscheiden was zu tun ist.

4) Die Cascade-Tiefenfalle

CASCADE ist mächtig – und genau das macht es gefährlich. Eine kaskadierende Löschung kann durch mehrere Ebenen propagieren wenn Kinder selbst wieder Kinder haben. Ein Beispiel mit drei Ebenen:

Cascade durch mehrere Ebenen

●

kunden (Eltern, Start)

1 Zeile

↓

bestellungen (FK → kunden, ON DELETE CASCADE)

6 Zeilen verknüpft

↓

bestellpositionen (FK → bestellungen, ON DELETE CASCADE)

23 Zeilen verknüpft

↓

positions_audit_log (FK → bestellpositionen, ON DELETE CASCADE)

187 Zeilen verknüpft

Ein einziges DELETE kann hier 1 + 6 + 23 + 187 = 217 Zeilen entfernen. Wenn du dir nicht bewusst bist welche kaskadierenden FKs existieren, kannst du versehentlich riesige Datenmengen vernichten – inklusive Audit-Logs, die rechtlich vielleicht aufbewahrt werden müssen. Best Practice: bei wichtigen Tabellen RESTRICT als Default verwenden, CASCADE nur dort wo das Kind ohne Eltern wirklich keinen Sinn hat (z.B. Bestellpositionen ohne Bestellung).

5) ON UPDATE in der Praxis

Das Pendant zu ON DELETE ist ON UPDATE: was passiert wenn der Primärschlüsselwert in der Eltern-Tabelle geändert wird? Bei Surrogate Keys (auto-generierten IDs) ist das selten – IDs ändern sich praktisch nie. Bei natürlichen Schlüsseln kann es aber durchaus vorkommen.

Beispiel: deine Tabelle laender nutzt den ISO-Code als PK (DE, FR, UK). Großbritannien ändert seinen Code – jetzt müssen alle Fremdschlüssel mit. Mit ON UPDATE CASCADE macht das DBMS das automatisch. Ohne wären alle Verweise plötzlich kaputt.

-- Ländertabelle mit natürlichem PK
CREATE TABLE laender (
  code CHAR(2) PRIMARY KEY,
  name VARCHAR(100)
);

-- Personen referenzieren das Land
CREATE TABLE personen (
  id BIGINT PRIMARY KEY,
  land_code CHAR(2),
  FOREIGN KEY (land_code) REFERENCES laender(code)
    ON UPDATE CASCADE -- ändert sich der Code, ändern sich alle FKs mit
    ON DELETE RESTRICT -- Land darf nicht gelöscht werden solange Personen drin sind
);

Diese asymmetrische Wahl – CASCADE beim UPDATE, RESTRICT beim DELETE – ist sehr gängig. Updates am Schlüssel sollen propagieren, aber Löschungen sollen ausgehandelt werden.

6) Welche Option soll ich wählen? Entscheidungshilfe

Drei Fragen helfen dir die richtige Option zu finden. Klick deine Antwort und du bekommst eine Empfehlung:

Wähle das passende ON DELETE-Verhalten

Diese Heuristik fängt 80% der Fälle ein. Spezialfälle: rechtliche Aufbewahrungspflichten zwingen oft zu RESTRICT (Audit-Logs dürfen nicht durch CASCADE verschwinden). Soft-Delete-Muster (eine Spalte deleted_at statt echtem DELETE) umgeht die ganze Frage – das Eltern bleibt physisch, ist nur logisch gelöscht. Dazu mehr in K35b zu Trigger-Patterns.

7) Constraint-Checks deaktivieren – manchmal nötig

Es gibt Situationen in denen die strengen Constraints im Weg stehen: ein großer Import aus einem alten System, bei dem die Daten in einer Reihenfolge kommen die referenzielle Integrität temporär verletzt. Oder ein TRUNCATE aller Tabellen für ein Reset des Test-Datenbestands. Dafür kann man Constraints kurzfristig deaktivieren:

-- MySQL: Foreign Key Checks temporär aus
SET FOREIGN_KEY_CHECKS = 0;
-- ... viele Inserts oder DELETEs in beliebiger Reihenfolge ...
SET FOREIGN_KEY_CHECKS = 1;

-- PostgreSQL: pro Constraint deferrable schalten
SET CONSTRAINTS ALL DEFERRED;
-- ... Operationen ...
COMMIT;

Wichtig: das ist eine scharfe Klinge. Nach dem Wiedereinschalten prüft das DBMS NICHT rückwirkend ob die jetzt vorhandenen Daten konsistent sind. Du kannst inkonsistente Daten hinterlassen und das DBMS würde es nicht merken. Faustregel: solche Schalter nur in Wartungsfenstern, mit Backup, und mit anschließendem Konsistenz-Check (z.B. einem manuellen JOIN der prüft ob alle FKs noch valid sind).

Zusammenfassung

Beim Löschen oder Ändern einer Zeile, auf die Fremdschlüssel verweisen, muss das DBMS wissen wie es reagieren soll. SQL bietet dafür fünf Verhaltensoptionen: CASCADE (Kinder mitlöschen/ändern), RESTRICT (Operation ablehnen), NO ACTION (wie RESTRICT, aber später geprüft), SET NULL (FK auf NULL), SET DEFAULT (FK auf Default-Wert). Definiert werden sie beim Erstellen des Fremdschlüssels: FOREIGN KEY ... ON DELETE ... ON UPDATE .... CASCADE ist mächtig aber gefährlich – kann durch viele Ebenen propagieren und große Datenmengen löschen. RESTRICT ist der sichere Default. ON UPDATE CASCADE ist sinnvoll wenn natürliche Schlüssel sich ändern können. Foreign Key Checks lassen sich temporär deaktivieren, sollten aber nur in Wartungsfenstern mit anschließender Konsistenzprüfung verwendet werden.