SQL ON DELETE CASCADE: Afhankelijke gegevens automatisch verwijderen

Gegevens verwijderen in SQL draait niet alleen om het weghalen van één rij. In een relationele database zijn tabellen vaak aan elkaar gekoppeld, en een delete in de ene tabel kan andere tabellen beïnvloeden. Om dit veilig te beheren, gebruikt SQL foreign key-constraints om te bepalen hoe gerelateerde data zich moet gedragen wanneer rijen worden verwijderd of bijgewerkt.

Een van de krachtigste opties voor verwijderen is de SQL-constraint ON DELETE CASCADE. In plaats van een delete te blokkeren of verweesde records achter te laten, vertelt deze regel de database om automatisch alle afhankelijke kindrijen te verwijderen wanneer een gekoppelde ouderrij wordt verwijderd.

In deze tutorial laat ik zien hoe de ON DELETE CASCADE-constraint werkt, waarom databases automatische cascaderende deletes ondersteunen, en wanneer het de juiste keuze is vergeleken met meer beperkende regels zoals ON DELETE RESTRICT.  Als je nieuw bent met SQL, begin dan met onze cursus Introduction to SQL, of de cursus Intermediate SQL als je al wat ervaring hebt. 

Wat is ON DELETE CASCADE in SQL?

Om te begrijpen hoe de foreign key-constraint ON DELETE CASCADE werkt, leg ik eerst referentiële integriteit en de CASCADE-clausule uit.

Referentiële integriteit en foreign keys

In relationele databases zijn tabellen verbonden via foreign keys, die een kolom in de ene tabel (het kind) koppelen aan een primary key in een andere tabel (de ouder). Foreign keys handhaven referentiële integriteit om te garanderen dat een kindrij altijd verwijst naar een geldige ouderrij. 

Zonder deze handhaving zouden databases gemakkelijk eindigen met verweesde records, waarbij rijen verwijzen naar data die niet meer bestaat. Daarom bestaan er verwijderregels om de database precies te vertellen hoe met deze weeskinderen om te gaan voordat ze überhaupt optreden.

Wat CASCADE precies betekent

In SQL instrueert de constraint ON DELETE CASCADE de database om automatisch alle kindrijen te verwijderen die verwijzen naar de verwijderde ouderrij. De operatie wordt recursief getriggerd via foreign key-ketens, waarbij afhankelijke data in één atomische transactie wordt verwijderd.

Je kunt bijvoorbeeld een klantrecord in de database verwijderen, waarna dit doorcascadet om diens orders te wissen, wat op zijn beurt orderregels verwijdert — allemaal met één query.

Hoe ON DELETE CASCADE in de praktijk werkt

Nu je begrijpt wat CASCADE doet, kijken we hoe de database de logica toepast.

Relaties tussen ouder- en kindtabellen

Zoals je nu weet, heeft een database-relatie één tabel die als ouder fungeert en de primary key bevat, terwijl een andere als kind fungeert en die sleutel als foreign key opslaat. Wanneer je een DELETE-statement uitvoert op de oudertabel, controleert de database-engine eerst op foreign key-constraints om alle afhankelijkheden te identificeren via indexen voor snelheid.

Wat er gebeurt wanneer een ouderrij wordt verwijderd

Wanneer je een DELETE-opdracht uitvoert op een ouderrij, pauzeert de database de definitieve uitvoering om de foreign key-regels te controleren in de volgende volgorde:

• Identificatie: De engine bekijkt de foreign key-index om alle rijen in kindtabellen te vinden die overeenkomen met de te verwijderen ID.
• Uitvoering: Voorafgaand aan (of tijdens) het verwijderen van de ouderrij verwijdert de engine de geïdentificeerde kindrijen.
• Validatie: De database zorgt ervoor dat er geen verweesde rijen achterblijven. Als alle deletes slagen, wordt de transactie afgerond.

Let op dat het bovenstaande proces alleen plaatsvindt als je de constraint ON DELETE CASCADE hebt gedefinieerd, zoals we later in het artikel zullen zien.

ON DELETE CASCADE versus andere verwijderregels

In SQL zijn er verschillende DELETE-regels die de database vertellen hoe om te gaan met data die gerelateerd is aan verschillende tabellen. In dit gedeelte vergelijken we ON DELETE CASCADE met deze constraints.

CASCADE versus RESTRICT

Uit de bovenstaande uitleg blijkt dat CASCADE automatisch alle afhankelijke kindrijen verwijdert wanneer een ouderrij wordt weggehaald, zodat er geen verweesde records achterblijven.

De SQL-constraint RESTRICT daarentegen blokkeert de verwijderoperatie volledig als er nog kindrijen zijn die naar de ouderrij verwijzen. Deze aanpak voorkomt onbedoeld dataverlies. In het voorbeeld hieronder voorkomt de RESTRICT-constraint dat een klant wordt verwijderd als er orders zijn die naar die klant verwijzen.

-- 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
);Mogelijk gemaakt door

CASCADE versus SET NULL en SET DEFAULT

Waar CASCADE afhankelijke rijen volledig verwijdert, behouden SET NULL en SET DEFAULT de kindrijen op de volgende manieren:

Als je je constraint definieert als ON DELETE SET NULL, wordt de ouderrij verwijderd, maar worden de foreign key-waarden in de kindtabel op NULL gezet.

In het onderstaande voorbeeld wordt bij het verwijderen van een klant de bijbehorende orders niet verwijderd. In plaats daarvan wordt de kolom customer_id in de tabel orders automatisch op NULL gezet. Dit behoudt de orderrecords terwijl de relatie met de verwijderde klant wordt verbroken.

-- 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
);Mogelijk gemaakt door

Op vergelijkbare wijze worden met de optie SET DEFAULT gerelateerde orders niet verwijderd wanneer een klant wordt verwijderd. In plaats daarvan wordt de customer_id in de tabel orders teruggezet naar de gedefinieerde standaardwaarde. Zo blijven de orderrecords intact terwijl ze een vooraf gedefinieerde placeholder krijgen.

-- 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
);Mogelijk gemaakt door

Foreign keys maken met ON DELETE CASCADE

Om de constraint ON DELETE RESTRICT in SQL te gebruiken, moet je die opgeven wanneer je de foreign key-relatie tussen de ouder- en kindtabellen vastlegt.

CASCADE in tabelschemas

Je kunt de regel ON DELETE CASCADE in SQL definiëren tijdens het aanmaken van tabellen of door bestaande tabellen te wijzigen.

Het onderstaande voorbeeld laat zien hoe je de SQL-foreign key-constraint ON DELETE CASCADE toepast bij het definiëren van een nieuwe tabel. Met CASCADE verwijdert het wissen van een afdeling automatisch alle werknemers die aan die afdeling zijn gekoppeld.

-- 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
);Mogelijk gemaakt door

Als de tabel al bestaat, kun je een foreign key-constraint met ON DELETE CASCADE toevoegen. Als je vervolgens een afdeling verwijdert, worden automatisch alle gerelateerde werknemersrecords verwijderd.

-- 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 deletedMogelijk gemaakt door

Cascaderende ketens en meerlagige deletes

Een van de sterke punten van CASCADE is dat deletes niet stoppen bij één relatie. Als een kindtabel ook een oudertabel is voor een andere tabel, kan de delete zich door meerdere niveaus voortplanten, zogenaamde cascaderende ketens. Zo kan het verwijderen van een gebruiker orders verwijderen, die vervolgens orderregels wissen, die mogelijk gerelateerde audit-rijen verwijderen.

Daarom is het belangrijk om schemas die CASCADE gebruiken te documenteren en zorgvuldig te beoordelen. Omdat één delete-statement veel tabellen kan beïnvloeden, moet je de volledige afhankelijkheidsketen begrijpen voordat je cascaderend gedrag inschakelt.

Veelvoorkomende use-cases voor ON DELETE CASCADE

De constraint ON DELETE CASCADE in SQL is belangrijk in situaties waarin data in de kindtabel geen waarde heeft zonder het ouderrecord. Laten we kijken naar de toepassing in het beheer van echte databases.

Afhankelijke data automatisch opschonen

Je kunt ON DELETE CASCADE gebruiken om data op te schonen waar handmatig opruimen inefficiënt zou zijn. Denk aan tabellen zoals logs, historiek, audit-trails of many-to-many koppel-tabellen die vaak uitsluitend bestaan ter ondersteuning van een ouderrecord. Bijvoorbeeld:

• Een gebruiker verwijderen moet diens loginhistorie verwijderen
• Een order verwijderen moet de bijbehorende orderregels verwijderen
• Een product verwijderen moet entries in een product-categorie-koppeltabel verwijderen

Datalevenscyclusbeheer vereenvoudigen

Je kunt CASCADE ook gebruiken om regels voor de datalevenscyclus in de database onder te brengen. In plaats van applicatiecode te laten zoeken naar gerelateerde records, ze in de juiste volgorde te verwijderen en randgevallen en fouten af te handelen, handhaaft de database deze regels consistent en atomisch.

Op die manier zorgt CASCADE ervoor dat, ongeacht hoe een delete wordt getriggerd — via een applicatie, een script of een admin-query — het resultaat altijd consistent is.

Risico's en valkuilen van ON DELETE CASCADE

Hoewel de constraint ON DELETE CASCADE handig kan zijn, kan ze riskant zijn als ze onjuist wordt gebruikt. Het begrijpen van de veelvoorkomende valkuilen is belangrijk bij het ontwerpen van effectieve CASCADE-constraints.

Per ongeluk massaal verwijderen

Het meest voorkomende risico bij CASCADE is onbedoeld grootschalig verwijderen. Eén enkel DELETE-statement op een oudertabel kan geruisloos honderden of duizenden gerelateerde rijen verwijderen in meerdere tabellen. Dit kan leiden tot onverwacht dataverlies dat lastig te herstellen is als je de relatie tussen tabellen niet goed begreep. 

Zorg er als best practice altijd voor dat je de relatie tussen foreign keys in ouder- en kindtabellen kunt volgen. Als je het schema goed opzet, weet je welke tabellen door één delete beïnvloed worden.

Verborgen afhankelijkheden in complexe schemas

Naarmate databases groeien, verzamelen tabellen extra foreign keys, en kan documentatie uit de pas gaan lopen met de werkelijkheid. De uitdaging ontstaat wanneer CASCADE in zulke omgevingen wordt geïmplementeerd en deletes zich kunnen voortplanten door diep geneste afhankelijkheidsketens waar ontwikkelaars of beheerders zich niet van bewust zijn.

Uit mijn ervaring raad ik aan om CASCADE te combineren met duidelijke schema-documentatie en regelmatige beoordeling van foreign key-relaties.

Probeer onze skill track SQL Server for Database Administrators als je regelmatig databases ontwerpt of onderhoudt in je werk. 

Databasespecifieke opmerkingen en gedrag

Hoewel het concept van ON DELETE CASCADE standaard is in relationele databases, handelt elke engine de onderliggende uitvoering net iets anders af.

Gedrag van PostgreSQL, MySQL en SQL Server

PostgreSQL, MySQL en SQL Server ondersteunen allemaal ON DELETE CASCADE en implementeren het op dezelfde manier, met de volgende uitzonderingen:

• MySQL: CASCADE werkt goed met InnoDB, maar vermijdt circulaire cascades

• PostgreSQL: Meest flexibel en permissief met cascaderende delete-ketens

• SQL Server: Veiliger maar restrictiever vanwege regels rond meerdere cascadepaden

Transactiebereik en rollback-veiligheid

Cascaderende deletes worden uitgevoerd binnen dezelfde transactie als het oorspronkelijke delete-statement. Als de database een fout tegenkomt bij het verwijderen van een kindrij, faalt de hele operatie. De ouderrij wordt niet verwijderd en eventueel al verwijderde kindrijen worden teruggezet.

Dit transactionele gedrag biedt een belangrijk vangnet. Het stelt je in staat om cascaderende deletes te testen, te beoordelen of terug te draaien voordat je commit.

De juiste ON DELETE-strategie kiezen

Je weet nu dat de juiste verwijderstrategie afhangt van hoe je data gerelateerd is en hoe je wilt dat die zich in de tijd gedraagt. Overweeg de volgende richtlijnen om de juiste DELETE-keuze voor je schema te maken.

Wanneer CASCADE de juiste keuze is

Gebruik de constraint ON DELETE CASCADE wanneer de kinddata volledig afhankelijk is van de ouder voor betekenis of bestaan. Je kunt het bijvoorbeeld gebruiken in:

• Koppeltabellen in many-to-many-relaties
• Tijdelijke of afgeleide data, zoals logs of historische entries

In de bovenstaande gevallen zorgt automatische opschoning voor consistentie en vermindert het de hoeveelheid handmatige deletelogica in de applicatielaag.

Ik raad je aan onze cursus Joining Data in SQL te volgen om de verschillende soorten joins in SQL te leren en te werken met verschillende gerelateerde tabellen in de database.

Wanneer je CASCADE moet vermijden

Vermijd de constraint ON DELETE CASCADE in je schema als de kinddata een zelfstandige waarde heeft of bewaard moet blijven voor juridische, audit- of analytische doeleinden. Gebruik bijvoorbeeld geen CASCADE bij:

• Financiële gegevens en facturen
• Auditlogs en compliance-data, zoals zorgdata.

Voor zulke gevallen raad ik restrictieve regels aan, zoals RESTRICT, SET NULL, of door de applicatie beheerde deletes om meer controle en zichtbaarheid te krijgen.

Conclusie

De SQL-constraint ON DELETE CASCADE is een krachtig automatiseringshulpmiddel dat referentiële integriteit bewaart door automatisch afhankelijke data te verwijderen wanneer een ouderrij wordt verwijderd. Bij juist gebruik vereenvoudigt het opschonen, vermindert het handmatige deletelogica en zorgt het voor consistent gedrag in applicaties en scripts. Wees altijd voorzichtig met CASCADE als je de relaties in het databaseschema niet volledig begrijpt, om onbedoelde deletes te voorkomen.

Ik raad je aan onze cursus Database Design te volgen, waarin je leert om databases te maken en te beheren en het juiste DBMS voor jouw behoeften te kiezen. Ik raad ook aan om onze Associate Data Engineer in SQL-carrièreroute te proberen om de basis van data-engineering en datawarehousing te leren.