SQL BEIM LÖSCHEN KASKADIEREN: Abhängige Daten automatisch löschen

Beim Löschen von Daten in SQL geht's nicht nur darum, eine einzelne Zeile zu entfernen. In einer relationalen Datenbank sind Tabellen oft miteinander verknüpft, und das Löschen in einer Tabelle kann sich auf andere Tabellen auswirken. Um das sicher zu machen, nutzt SQL Fremdschlüsselbeschränkungen, um festzulegen, wie sich zugehörige Daten verhalten sollen, wenn Zeilen gelöscht oder aktualisiert werden.

Eine der mächtigsten Löschoptionen ist die SQL- ON DELETE CASCADE -Einschränkung. Anstatt eine Löschung zu blockieren oder verwaiste Datensätze zurückzulassen, sagt diese Regel der Datenbank, dass sie automatisch alle abhängigen untergeordneten Zeilen löschen soll, wenn eine referenzierte übergeordnete Zeile gelöscht wird.

In diesem Tutorial zeige ich dir, wie die Einschränkung „ ON DELETE CASCADE “ funktioniert, warum Datenbanken automatische kaskadierende Löschungen unterstützen und wann sie im Vergleich zu restriktiveren Regelnwie „ON DELETE RESTRICT“die richtige Wahl ist.  Wenndu noch keine Erfahrung mit SQL hast, fang mit unserem Kurs „Einführung in SQL“an . Wenn du schon ein bisschen Erfahrung hast, kannst du mit dem Kurs „SQL für Fortgeschrittene“ loslegen. 

Was bedeutet ON DELETE CASCADE in SQL?

Um zu verstehen, wie die Fremdschlüsselbeschränkung „ ON DELETE CASCADE “ funktioniert, erkläre ich dir erst mal die referenzielle Integrität und die Klausel „ CASCADE “.

Referenzielle Integrität und Fremdschlüssel

In relationalen Datenbanken sind Tabellen über Fremdschlüssel miteinander verbunden, die eine Spalte in einer Tabelle (der untergeordneten Tabelle) mit einem Primärschlüssel in einer anderen Tabelle (der übergeordneten Tabelle) verknüpfen. Fremdschlüssel sorgen dafür, dass die Referenzintegrität gewahrt bleibt, damit eine untergeordnete Zeile immer auf eine gültige übergeordnete Zeile verweist. 

Ohne diese Durchsetzung könnten Datenbanken schnell verwaiste Datensätze haben, wo Zeilen auf Daten verweisen, die nicht mehr da sind. Deshalb gibt's Löschregeln, die der Datenbank genau sagen, wie sie mit diesen verwaisten Daten umgehen soll, bevor sie überhaupt entstehen.

Was CASCADE eigentlich bedeutet

In SQL sagt die Einschränkung „ ON DELETE CASCADE “ der Datenbank, dass sie automatisch alle untergeordneten Zeilen löschen soll, die auf die gelöschte übergeordnete Zeile verweisen. Es löst den Vorgang rekursiv über Fremdschlüsselketten aus und löscht abhängige Daten in einer atomaren Transaktion in einem Schritt.

Du kannst zum Beispiel einen Kundeneintrag in der Datenbank löschen, wodurch auch die Bestellungen gelöscht werden, was wiederum die Bestellpositionen in einer einzigen Abfrage löscht.

Wie ON DELETE CASCADE in der Praxis funktioniert

Jetzt, wo du weißt, was „ CASCADE “ macht, schauen wir uns an, wie die Datenbank diese Logik anwendet.

Beziehungen zwischen Eltern- und Kind-Tabellen

Wie du jetzt weißt, haben Datenbankbeziehungen eine Tabelle, die als übergeordnete Tabelle fungiert undden Primärschlüsselenthält,während eine andere Tabelle als untergeordnete Tabelle fungiert und diesen Schlüssel als Fremdschlüssel speichert. Wenn du eine „ DELETE “-Anweisung für die übergeordnete Tabelle abfragst, schaut die Datenbank-Engine zuerst nach Fremdschlüsselbeschränkungen, um alle Abhängigkeiten zu finden. Das passiert über Indizierung, damites schneller geht.

Was passiert, wenn eine übergeordnete Zeile gelöscht wird?

Wenn du einen Befehl „ DELETE “ auf einer übergeordneten Zeile ausführst, hält die Datenbank die endgültige Ausführung an, um die Fremdschlüsselregeln in der folgenden Reihenfolge zu überprüfen:

• Identifizierung: Die Engine schaut im Fremdschlüsselindex nach allen Zeilen in untergeordneten Tabellen, die mit der zu löschenden ID übereinstimmen.
• Durchführung: Vor (oder während) dem Löschen der übergeordneten Zeile löscht die Engine die identifizierten untergeordneten Zeilen.
• Validierung: Die Datenbank sorgt dafür, dass keine verwaisten Zeilen übrig bleiben. Wenn alle Löschungen geklappt haben, ist die Transaktion abgeschlossen.

Beachte, dass der oben beschriebene Prozess nur dann stattfindet, wenn du die Einschränkung „ ON DELETE CASCADE ” definiert hast, wie wir später in diesem Artikel sehen werden.

ON DELETE CASCADE vs. Andere Löschregeln

In SQL gibt's verschiedene Beziehungsregeln ( DELETE ), die der Datenbank sagen, wie sie mit Daten aus verschiedenen Tabellen umgehen soll. In diesem Abschnitt schauen wir uns „ ON DELETE CASCADE “ mit diesen Einschränkungen an.

CASCADE gegen EINSCHRÄNKEN

Aus der obigen Erklärung geht hervor, dass „ CASCADE “ automatisch alle abhängigen untergeordneten Zeilen löscht, wenn eine übergeordnete Zeile entfernt wird, sodass keine verwaisten Datensätze zurückbleiben.

Die SQL- RESTRICT -Einschränkung stoppt die Löschoperation komplett, wenn irgendwelche untergeordneten Zeilen noch auf die übergeordnete Zeile verweisen. Dieser Vorgang stellt sicher, dass keine Daten versehentlich verloren gehen. Im folgenden Beispiel verhindert die Einschränkung „ RESTRICT “ (Kunden, die auf diese Bestellung verweisen, können nicht gelöscht werden), dass ein Kunde gelöscht wird, wenn es Bestellungen gibt, die auf diesen Kunden verweisen.

-- Parent table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- Child table with ON DELETE RESTRICT
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE RESTRICT
);Bereitgestellt von

CASCADE gegen SET NULL und SET DEFAULT

Während „ CASCADE “ abhängige Zeilen komplett löscht, behalten „ SET NULL “ und „ SET DEFAULT “ die untergeordneten Zeilen auf folgende Weise bei:

Wenn du deine Einschränkung als „ ON DELETE SET NULL “ festlegst, wird die übergeordnete Zeile gelöscht, aber die Fremdschlüsselwerte in der untergeordneten Tabelle werden auf „ NULL “ gesetzt.

Im folgenden Beispiel werden beim Löschen eines Kunden die dazugehörigen Bestellungen nicht entfernt. Stattdessen wird die Spalte „ customer_id ” in der Tabelle „ orders ” automatisch auf „ NULL ” gesetzt. Dadurch bleiben die Auftragsdaten erhalten, während die Verbindung zum gelöschten Kunden getrennt wird.

-- Child table using ON DELETE SET NULL
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NULL,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE SET NULL
);Bereitgestellt von

Genauso werden bei der Option „ SET DEFAULT ” beim Löschen eines Kunden die dazugehörigen Bestellungen nicht entfernt. Stattdessen wird das Feld „ customer_id “ in der Tabelle „ orders “ auf den festgelegten Standardwert zurückgesetzt. Dadurch bleiben die Bestelldatensätze intakt, während ihnen ein vordefinierter Platzhalter zugewiesen wird.

-- Child table using ON DELETE SET DEFAULT
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT DEFAULT 0,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE SET DEFAULT
);Bereitgestellt von

Fremdschlüssel mit ON DELETE CASCADE erstellen

Um die Einschränkung „ ON DELETE RESTRICT “ in SQL zu nutzen, musst du sie beim Einrichten der Fremdschlüsselbeziehung zwischen der übergeordneten und der untergeordneten Tabelle angeben.

CASCADE in Tabellenschemata

Du kannst die Regel „ ON DELETE CASCADE “ in SQL entweder beim Erstellen von Tabellen oder durch Ändern bestehender Tabellen festlegen.

Das folgende Beispiel zeigt, wie du die Fremdschlüsselbeschränkung SQL- ON DELETE CASCADE beim Definieren einer neuen Tabelle anwendest. Mit „ CASCADE “ werden beim Löschen einer Abteilung automatisch alle Mitarbeiter dieser Abteilung entfernt.

-- Stores department details
CREATE TABLE departments (
    department_id INT PRIMARY KEY, 
    department_name VARCHAR(100)
);

-- Automatically deletes employees when their department is deleted
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,      -- Unique employee identifier
    department_id INT, 
    CONSTRAINT fk_employees_departments
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
        ON DELETE CASCADE
);Bereitgestellt von

Wenn die Tabelle schon da ist, kannst du eine Fremdschlüsselbeschränkung mit „ ON DELETE CASCADE “ hinzufügen. Wenn du also eine Abteilung löschst, werden automatisch alle dazugehörigen Mitarbeiterdaten gelöscht.

-- Adds a foreign key constraint after the table already exists
ALTER TABLE employees
ADD CONSTRAINT fk_employees_departments
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
ON DELETE CASCADE;    -- Deletes employees when the related department is deletedBereitgestellt von

Kaskadierende Ketten und mehrstufige Löschungen

Eine der coolen Sachen an „ CASCADE “ ist, dass das Löschen nicht bei einer einzigen Beziehung aufhört. Wenn eine untergeordnete Tabelle auch eine übergeordnete Tabelle für eine andere Tabelle ist, kann sich das Löschen über mehrere Ebenen ausbreiten, die als Kaskadenketten bezeichnet werden. Wenn du zum Beispiel einen Benutzer löschst, werden vielleicht auch Bestellungen gelöscht, was dann zum Löschen von Bestellpositionen führt, wodurch wiederum zugehörige Prüfzeilen gelöscht werden können.

Deshalb ist es wichtig, Schemata, die „ CASCADE “ verwenden, zu dokumentieren und genau zu überprüfen. Da eine einzelne Löschanweisung viele Tabellen beeinflussen kann, solltest du die ganze Abhängigkeitskette verstehen, bevor du das Kaskadenverhalten aktivierst.

Häufige Anwendungsfälle für ON DELETE CASCADE

Die Einschränkung „ ON DELETE CASCADE “ in SQL ist wichtig, wenn die Daten in der untergeordneten Tabelle ohne den übergeordneten Datensatz keinen Sinn ergeben. Schauen wir uns mal an, wie das in der echten Datenbankverwaltung funktioniert.

Abhängige Daten automatisch bereinigen

Du kannst „ ON DELETE CASCADE “ nutzen, um Daten zu bereinigen, bei denen eine manuelle Bereinigung zu aufwendig wäre. Dazu gehören Tabellen wie Protokolle, Verlaufsdatensätze, Prüfpfade oder viele-zu-viele-Verbindungstabellen, die oft nur dazu da sind, einen übergeordneten Datensatz zu unterstützen. Zum Beispiel:

• Wenn du einen Nutzer löschst, sollte sein Login-Verlauf auch weg sein.
• Wenn du eine Bestellung löschst, sollten auch die Artikel weg sein.
• Wenn du ein Produkt löschst, sollten die Einträge in der Zuordnungstabelle für Produktkategorien auch weg sein.

Das Datenlebenszyklusmanagement einfacher machen

Du kannst auch „ CASCADE “ nutzen, um Datenlebenszyklusregeln in die Datenbank zu verschieben. Anstatt sich auf den Anwendungscode zu verlassen, um verwandte Datensätze zu finden, sie in der richtigen Reihenfolge zu löschen und Sonderfälle und Fehler zu behandeln, setzt die Datenbank diese Regeln konsequent und atomar durch.

So stellt „ CASCADE “ sicher, dass das Ergebnis immer gleich bleibt, egal wie das Löschen passiert – ob durch eine App, ein Skript oder eine Admin-Abfrage.

Risiken und Fallstricke von ON DELETE CASCADE

Auch wenn die Einschränkung „ ON DELETE CASCADE “ praktisch sein kann, kann sie bei falscher Anwendung riskant sein. Es ist wichtig, die üblichen Fallstricke zu kennen, wenn man effiziente Einschränkungen für die CASCADE entwickelt.

Versehentliches Löschen mehrerer Dateien

Das häufigste Risiko bei der automatischen Löschung von Daten ( CASCADE ) sind unbeabsichtigte Löschungen in großem Umfang. Eine einzige Anweisung „ DELETE “ in einer übergeordneten Tabelle kann stillschweigend Hunderte oder Tausende von zugehörigen Zeilen in mehreren Tabellen löschen. Das kann zu unerwartetem Datenverlust führen, der schwer wiederherzustellen ist, wenn du die Beziehungen zwischen den Tabellen nicht richtig verstanden hast. 

Als bewährte Vorgehensweise solltest du immer sicherstellen, dass du die Beziehung zwischen Fremdschlüsseln in über- und untergeordneten Tabellen nachvollziehen kannst. Wenn du das Schema richtig hinbekommst, weißt du, welche Tabellen von einem einzelnen Löschvorgang betroffen wären.

Versteckte Abhängigkeiten in komplizierten Schemata

Wenn Datenbanken wachsen, sammeln sich in den Tabellen immer mehr Fremdschlüssel an, und die Dokumentation kann dann nicht mehr ganz mit der Realität mithalten. Die Herausforderung kommt, wenn „ CASCADE “ in solchen Umgebungen eingesetzt wird und Löschungen sich durch tief verschachtelte Abhängigkeitsketten ausbreiten können, die Entwicklern oder Admins nicht bekannt sind.

Aus meiner Erfahrung empfehle ich dir, „ CASCADE ” mit einer klaren Schema-Dokumentation und einer regelmäßigen Überprüfung der Fremdschlüsselbeziehungen zu kombinieren.

Probiermal unserenLernpfad „SQL Server für Datenbankadministratoren” aus,wenn du regelmäßig Datenbanken entwirfst oder pflegst. 

Datenbankspezifische Hinweise und Verhalten

Obwohl das Konzept der „ ON DELETE CASCADE “ in relationalen Datenbanken Standard ist, gibt es bei der Ausführung durch die einzelnen Engines kleine Unterschiede.

Verhalten von PostgreSQL, MySQL und SQL Server

PostgreSQL, MySQL und SQL Serverunterstützen alle„ ON DELETE CASCADE ” und setzen es auf die gleiche Weise um, mit folgenden Ausnahmen:

• MySQL:CASCADE funktioniert gut mit InnoDB, vermeidet aber zirkuläre Kaskaden

• PostgreSQL: Super flexibel und tolerant bei Ketten von Löschvorgängen

• SQL Server: Sicherer, aber eingeschränkter wegen der Regeln für mehrere Kaskadenpfade

Transaktionsumfang und Rollback-Sicherheit

Kaskadierende Löschungen werden in derselben Transaktion wie die ursprüngliche Löschanweisung gemacht. Wenn die Datenbank beim Versuch, eine untergeordnete Zeile zu löschen, auf einen Fehler stößt, schlägt der ganze Vorgang fehl. Die übergeordnete Zeile wird nicht gelöscht, und alle bereits entfernten untergeordneten Zeilen werden wiederhergestellt.

Dieses Transaktionsverhalten ist echt ein wichtiges Sicherheitsnetz. Damit kannst du vor dem Commit kaskadierende Löschungen testen, überprüfen oder rückgängig machen.

Die richtige ON DELETE-Strategie auswählen

Mittlerweile weißt du, dass die Wahl der richtigen Löschstrategie davon abhängt, wie deine Daten miteinander zusammenhängen und wie sie sich im Laufe der Zeit verhalten sollen. Beachte die folgenden Tipps, um die richtige Wahl für dein Schema zu treffen: DELETE.

Wann CASCADE die richtige Wahl ist

Du solltest die Einschränkung „ ON DELETE CASCADE “ verwenden, wenn die untergeordneten Daten in Bezug auf ihre Bedeutung oder Existenz vollständig von den übergeordneten Daten abhängig sind. Du kannst es zum Beispiel so nutzen:

• Tabellen in Mehr-zu-Mehr-Beziehungen verbinden
• Vorübergehende oder abgeleitete Daten, wie Protokolle oder Verlaufseinträge

In den oben genannten Fällen sorgt die automatische Bereinigung für Konsistenz und reduziert gleichzeitig die manuelle Löschlogik, die in der Anwendungsschicht nötig ist.

Ich empfehle dir , unseren Kurs „Joining Data in SQL” zu machen ,um die verschiedenen Arten von Joins in SQL kennenzulernen und zu lernen, wie man mit verschiedenen verwandten Tabellen in der Datenbank arbeitet.

Wann sollte man CASCADE vermeiden?

Du solltest die Einschränkung „ ON DELETE CASCADE “ in deinem Schema aber vermeiden, wenn die untergeordneten Daten einen eigenständigen Wert haben oder aus rechtlichen, prüfungstechnischen oder analytischen Gründen erhalten bleiben müssen. Du darfst CASCADE zum Beispiel nicht verwenden in:

• Finanzunterlagen und Rechnungen
• Auditprotokolle und Compliance-Daten, wie zum Beispiel Gesundheitsdaten.

Für solche Fälle empfehle ich, restriktive Regeln wie „ RESTRICT “, „ SET NULL “ oder anwendungsgesteuerte Löschungen zu nutzen, um mehr Kontrolle und Transparenz zu haben.

Fazit

Die SQL- ON DELETE CASCADE -Einschränkung ist ein echt cooles Tool, das die referenzielle Integrität sicherstellt, indem es automatisch abhängige Daten löscht, wenn eine übergeordnete Zeile weggeworfen wird. Wenn man es richtig einsetzt, macht es die Bereinigung einfacher, reduziert manuelles Löschen und sorgt für einheitliches Verhalten in allen Anwendungen und Skripten. Um versehentliche Löschungen zu vermeiden, sei immer vorsichtig, wenn du „ CASCADE “ benutzt, wenn du die Beziehungen im Datenbankschema nicht ganz verstehst.

Ich empfehle dir, unseren Kurs „Datenbankdesign“ zu machen. Da lernst du, wie man Datenbanken erstellt und verwaltet und das passende DBMS für deine Bedürfnisse auswählt. Ich empfehle dir auch, unseren Lernpfad „Associate Data Engineer in SQL“ auszuprobieren, um die Grundlagen des Data Engineering und Data Warehousing zu lernen.