SQL ON DELETE RESTRICT: Voorkom onbedoeld gegevensverlies

Gegevens verwijderen in SQL is meestal niet zo simpel als één rij weghalen. In relationele databases zijn tabellen vaak met elkaar verbonden, en het verwijderen van een record in de ene tabel kan impact hebben op een andere tabel. 

SQL biedt de ON DELETE-clausule, die bepaalt wat er moet gebeuren wanneer een gerefereerde rij wordt verwijderd. Na de ON DELETE-clausule kun je specificeren wat er gebeurt, zoals het blokkeren van de delete, het automatisch verwijderen van de afhankelijke rij of het bijwerken van de gerefereerde data.

In deze tutorial laat ik je de ON DELETE RESTRICT-restrictie zien, die voorkomt dat een ouderrij wordt verwijderd als er nog afhankelijke kindrijen bestaan. Ik ga ook uitgebreid in op hoe de restrictie werkt, waarom databases die afdwingen en wanneer het de juiste keuze is vergeleken met andere delete-regels. Zoek je juist het tegenovergestelde gedrag — waarbij het verwijderen van een ouderrij automatisch gerelateerde records weghaalt — bekijk dan onze gids over SQL ON DELETE CASCADE.

Wat is ON DELETE RESTRICT in SQL?

De ON DELETE RESTRICT-restrictie in SQL is een veiligheidsfunctie van de database. Het zorgt ervoor dat je een "ouder"-record niet kunt verwijderen als er nog "kind"-records naar verwijzen.

Let op: Hoewel databases zoals PostgreSQL en MySQL het RESTRICT-trefwoord direct ondersteunen, dwingt SQL Server hetzelfde restrictiegedrag af met ON DELETE NO ACTION.

Referentiële integriteit en foreign keys

Referentiële integriteit is het principe dat relaties tussen tabellen altijd geldig moeten blijven. Foreign keys handhaven dit door te eisen dat waarden in een kindtabel overeenkomen met bestaande rijen in een oudertabel. Bijvoorbeeld: een DepartmentID in de tabel Employees kan verwijzen naar een specifieke ID in de tabel Departments.

Daarom bestaan SQL-DELETE-regels: het verwijderen van een ouderrij kan deze relatie verbreken. De database heeft duidelijke instructies nodig of de delete moet worden geblokkeerd, doorgegeven (cascade) of een andere actie op afhankelijke rijen moet worden toegepast.

Wat RESTRICT precies betekent

Wanneer je een foreign key definieert met ON DELETE RESTRICT, controleert de database op gerelateerde kindrijen voordat een delete wordt uitgevoerd. Als er een kindrecord bestaat, wordt de delete-operatie automatisch afgewezen.  In dit geval voorkomt RESTRICT dat de verwijdering plaatsvindt in plaats van de data te wijzigen, zoals we later in de tutorial zullen zien.

Hoe ON DELETE RESTRICT werkt in SQL

Om te begrijpen hoe de ON DELETE RESTRICT-restrictie werkt, moet je zien hoe de database deletes stap voor stap evalueert tijdens een transactie.

Relaties tussen ouder- en kindtabellen

In SQL bevat de oudertabel de primaire records, terwijl de kindtabel de afhankelijke records bevat. Je database kan bijvoorbeeld een oudertabel Categories hebben met een category_id "Electronics". De kindtabel Products heeft dan een record "Smartphone", gekoppeld aan de oudertabel via de category_id.

Wanneer je een DELETE-opdracht op de oudertabel uitvoert, scant de database-engine onmiddellijk alle gekoppelde kindtabellen om te zien of er rijen naar die specifieke ouder-ID verwijzen.

Wat gebeurt er als je beperkte rijen probeert te verwijderen?

Als je probeert een ouderr rij te verwijderen waar nog naar wordt verwezen, stopt de database de operatie en geeft een foutmelding. Deze foutmelding duidt op een schending van een foreign key-constraint.

Dit gedrag is bewust zo ontworpen: het dwingt je om bewust te besluiten om eerst de afhankelijke kindrijen te verwijderen of bij te werken, of om te heroverwegen of de ouderrij überhaupt verwijderd moet worden.

SQL ON DELETE RESTRICT vs. andere delete-regels

In een DBMS zijn er specifieke regels die bepalen hoe gegevens uit gerelateerde tabellen kunnen worden verwijderd. In deze sectie vergelijken we de ON DELETE RESTRICT-restrictie met de gangbare delete-regels in SQL, zodat je duidelijk ziet hoe elke regel gerelateerde data beïnvloedt.

RESTRICT vs. CASCADE in SQL

De SQL-RESTRICT-restrictie blokkeert het verwijderen van een ouderr rij als kindrijen ernaar verwijzen, zodat onbedoeld gegevensverlies wordt voorkomen. De volgende query laat zien hoe je RESTRICT implementeert bij het definiëren van het tabelschema. Het voorkomt dat een klant wordt verwijderd als er bestellingen 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

De SQL-CASCADE-restrictie zorgt er daarentegen voor dat een verwijdering automatisch alle afhankelijke kindrijen verwijdert. In het onderstaande voorbeeld maakt CASCADE het mogelijk om alle bestellingen die aan een klant zijn gekoppeld automatisch te verwijderen wanneer het klantrecord wordt verwijderd.

-- Child table with ON DELETE CASCADE
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 CASCADE
);Mogelijk gemaakt door

RESTRICT vs. SET NULL en SET DEFAULT

De SQL-constraints SET NULL en SET DEFAULT behouden kindrijen maar veranderen hoe ze naar de ouder verwijzen.

In de onderstaande query wordt bijvoorbeeld, wanneer een klant wordt verwijderd, de customer_id in gerelateerde bestellingen op NULL gezet, waardoor de bestelrecords behouden blijven maar de link naar de klant wordt verwijderd.

-- Child table using 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

Gebruik je de SET DEFAULT-restrictie, dan wordt bij het verwijderen van een klant de customer_id in gerelateerde bestellingen op de standaardwaarde gezet, zodat de bestelrecords behouden blijven en een vooraf gedefinieerde placeholderwaarde krijgen.

-- Child table using 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 RESTRICT in SQL

Om de ON DELETE RESTRICT-restrictie in SQL te implementeren, moet je die definiëren wanneer je de relatie tussen twee tabellen aanmaakt.

RESTRICT definiëren in tabelschema's

Je kunt het delete-gedrag in SQL opgeven bij het aanmaken van de tabel, of het achteraf toevoegen als je tabel al bestaat.

Het onderstaande voorbeeld laat zien hoe je de SQL-foreign key ON DELETE RESTRICT gebruikt in de CREATE TABLE-instructie bij het definiëren van een nieuwe tabel in SQL.

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

-- -- Prevents deleting a department with employees
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 RESTRICT 
);Mogelijk gemaakt door

De onderstaande query voegt dezelfde foreign key-regel toe aan een bestaande tabel employees via de SQL-instructie ALTER TABLE, om te voorkomen dat afdelingen worden verwijderd waar nog door werknemers naar wordt verwezen.

-- 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 RESTRICT;    -- Blocks deletion of departments that have employeesMogelijk gemaakt door

Standaard delete-gedrag in SQL

In veel populaire databasesystemen, zoals SQL Server, PostgreSQL en MySQL, is RESTRICT het standaardgedrag. Als je een foreign key definieert zonder een ON DELETE-clausule op te geven, gedraagt de database zich meestal alsof je ON DELETE NO ACTION of ON DELETE RESTRICT hebt geschreven. Dit ontwerp van de database-engine zorgt ervoor dat je een foutmelding krijgt in plaats van per ongeluk grote hoeveelheden gerelateerde data te verwijderen.

Veelvoorkomende use-cases voor ON DELETE RESTRICT in SQL

In praktijksituaties wordt de SQL-ON DELETE RESTRICT-restrictie toegepast waar dataintegriteit expliciet toezicht vereist vóór verwijderingen.

Kritieke referentiegegevens beschermen

De SQL-RESTRICT-restrictie wordt vaak gebruikt voor tabellen die kernreferentiegegevens vertegenwoordigen, zoals gebruikers, accounts, producten of opzoekwaarden. Deze records worden vaak gedeeld door meerdere onderdelen van het systeem, en automatisch verwijderen kan leiden tot wijdverspreid gegevensverlies of inconsistenties.

Zo is het meestal onveilig om een gebruiker te verwijderen die nog bestellingen, auditlogs of permissies bezit. Met de SQL-ON DELETE RESTRICT-restrictie blokkeert de database de delete en dwingt je om eerst die afhankelijkheden op te lossen, waardoor de integriteit van historische en transactionele data behouden blijft.

Expliciete opschoonworkflows afdwingen

De SQL-RESTRICT-restrictie is ook handig wanneer je wilt dat opschonen bewust en traceerbaar gebeurt. Het dwingt ontwikkelaars en beheerders om weloverwogen met data om te gaan. In plaats van de database automatisch records te laten verwijderen, dwingt de RESTRICT-restrictie je om de afhankelijke data te beoordelen en te beslissen of je de kindrecords verwijdert, aan een nieuwe ouder toewijst of de operatie annuleert.

DELETE-fouten oplossen die door RESTRICT worden veroorzaakt in SQL

Wanneer de ON DELETE RESTRICT-restrictie een delete blokkeert, kan de resulterende foutmelding verwarrend zijn als je die niet verwacht. Door deze fouten te begrijpen, kun je ze veilig oplossen.

Fouten door constraint-schending begrijpen

Wanneer je probeert een ouderr rij te verwijderen waar nog naar wordt verwezen, geeft de database een schending van een foreign key-constraint. Zo ziet het bericht eruit in verschillende databasesystemen:

• PostgreSQL: ERROR: update or delete on table "parent_table" violates foreign key constraint "fk_name" on table "child_table"

• MySQL: Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails…

• SQL Server: The DELETE statement conflicted with the REFERENCE constraint "FK_Name". The conflict occurred in database "DBName", table "dbo.ChildTable", column 'ParentID'.

In alle databases geeft de foutmelding het volgende aan:

• Welke foreign key-constraint is geschonden
• Welke tabel als kind fungeert
• Welke sleutelwaarde nog wordt gerefereerd

Geblokkeerde deletes veilig oplossen

Krijg je de foutmelding van een geblokkeerde delete, maar wilde je de ouderr rij verwijderen, dan raad ik je een van de volgende opties aan:

Verwijder eerst het kindrecord: Als de afhankelijke data niet meer nodig is, voer dan eerst een DELETE-instructie uit op de kindrijen en verwijder daarna het ouderrecord.

-- Step 1: Remove dependencies
DELETE FROM Orders WHERE CustomerID = 501; 
-- Step 2: Now the parent delete will work
DELETE FROM Customers WHERE CustomerID = 501;Mogelijk gemaakt door

Werk kindverwijzingen bij: Als je de kindrijen wilt behouden maar de ouder wilt verwijderen, werk de kindrijen dan bij zodat ze naar een andere ouder-ID verwijzen.

-- Reassign orders to a "General" customer account (ID 999)
UPDATE Orders SET CustomerID = 999 WHERE CustomerID = 501;Mogelijk gemaakt door

Wijzig de restrictie: In zeldzame beheerscenario's kun je foreign key-checks tijdelijk uitschakelen. Dit is risicovol en kan leiden tot datacorruptie als het niet met de grootste zorg wordt uitgevoerd.

Database-specifieke opmerkingen en gedrag

Hoewel het concept van de ON DELETE RESTRICT-restrictie in SQL standaard is in SQL-databases, gaan verschillende engines iets anders om met de timing en interne logica.

Gedrag van PostgreSQL, MySQL en SQL Server

In PostgreSQL, MySQL en SQL Server voorkomt de ON DELETE RESTRICT-restrictie dat een ouderr rij wordt verwijderd als er gerelateerde kindrijen bestaan. Wel zijn er kleine syntactische verschillen tussen deze engines:

MySQL: Volgt de SQL-RESTRICT-restricties strikt. Als je geen regel opgeeft, is de standaard RESTRICT.

-- MySQL: RESTRICT is the default if no rule is specified
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);Mogelijk gemaakt door

PostgreSQL: Ondersteunt zowel RESTRICT als NO ACTION. Hoewel ze bijna identiek werken, is RESTRICT strikt onmiddellijk, terwijl NO ACTION uitgestelde checks toestaat.

-- PostgreSQL: NO ACTION (can be deferred)
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE NO ACTION
);Mogelijk gemaakt door

SQL Server: Gebruikt de NO ACTION-restrictie als equivalent van het standaard RESTRICT-gedrag. Het gebruikt het trefwoord RESTRICT niet expliciet in T-SQL, maar bereikt hetzelfde resultaat door de delete te voorkomen.

-- SQL Server: Uses NO ACTION instead of 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 NO ACTION
);Mogelijk gemaakt door

Ik raad aan om ons Introduction to SQL Server en Creating PostgreSQL Databases-cursussen te volgen om meer te leren over de verschillen in de SQL-dialecten bij het maken van databases.

Uitgestelde restricties en transactiegdrag

In geavanceerde databaseconfiguraties, zoals in PostgreSQL of Oracle, kun je uitgestelde restricties tegenkomen. Dit betekent dat de restrictie wordt gecontroleerd bij het committen van de transactie, in plaats van direct wanneer de DELETE-instructie wordt uitgevoerd.

In dergelijke systemen kan een delete die normaal door RESTRICT zou worden geblokkeerd, tijdelijk lijken te slagen binnen een transactie, om vervolgens te falen wanneer je probeert te committen. Dit maakt complexe, meerstapsoperaties mogelijk, zoals het verwijderen en opnieuw invoegen van gerelateerde data binnen dezelfde transactie, terwijl aan het einde toch de integriteit wordt afgedwongen.

Onze Introduction to Oracle SQL-cursus helpt je beter te begrijpen hoe Oracle queries verwerkt.

De juiste ON DELETE-strategie kiezen

De juiste restrictie selecteren helpt je een balans te vinden tussen dataintegriteit en gebruiksgemak van de applicatie. Overweeg de volgende scenario's om de passende methode met de ON DELETE-clausule te kiezen.

Wanneer RESTRICT de veiligste keuze is

Gebruik de SQL-ON DELETE RESTRICT-restrictie wanneer:

• De data zeer waardevol is: De ouderrecords zijn belangrijk en veelgebruikt in de database.

• Handmatige review vereist is: Je een mens of specifieke bedrijfslogica wilt dwingen te beslissen wat er met afhankelijke data gebeurt.

• “Stille” verwijderingen voorkomen: Je wilt zeker weten dat een ontwikkelaar niet per ongeluk duizenden rijen wist met één enkele CASCADE-delete.

Ik raad je aan om de SQL Server for Database Administrators skill track te proberen als je regelmatig databases ontwerpt of beheert in je werk. 

Wanneer je alternatieven overweegt

Als de RESTRICT-restrictie te star is voor je systeem, overweeg dan het volgende:

• Gebruik de CASCADE-restrictie wanneer het kindrecord geen betekenis heeft zonder het ouderrecord.

• Gebruik de SET NULL-restrictie wanneer de relatie optioneel is.

• Gebruik de SET DEFAULT-r estrictie wanneer je een “placeholder” hebt die alle verweesde records moet overnemen om het systeem soepel te laten draaien.

Conclusie

De SQL-ON DELETE RESTRICT-restrictie is een beschermende foreign key-regel die dataintegriteit boven gemak stelt door het verwijderen van ouderrijen te blokkeren wanneer er nog afhankelijke kindrecords bestaan. Door doelbewuste opschoonstrategieën en duidelijke eigenaarschap van delete-operaties te stimuleren, fungeert de SQL-RESTRICT-restrictie als veiligheidsvoorziening in relationeel ontwerp. Kies als best practice altijd SQL-delete-regels op basis van bedrijfslogica en datasemantiek, in plaats van gemak of kortetermijnbehoeften.

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