Een tester wil een faseringstabel leegmaken voordat er opnieuw wordt getest. Moet het DELETE, TRUNCATE of DROP zijn? De verkeerde keuze kan een langzame klus betekenen, een ontbrekende tafel of, tijdens de productie, een zeer slechte middag.

We hebben de onderstaande DELETE- en DROP-voorbeelden uitgevoerd in SQLite 3.50, en de uitvoer komt uit die run. SQLite heeft geen TRUNCATE commando, dus de TRUNCATE-opmerkingen zijn gebaseerd op de officiële PostgreSQL-, MySQL- en SQL Server-documentatie in plaats van op onze eigen test.

Het korte antwoord

• Wilt u enkele rijen verwijderen? Gebruik DELETE ... WHERE.
• Wilt u een tafel leegmaken, maar deze behouden? Gebruik TRUNCATE, waar dit wordt ondersteund.
• Moet de tafel voorgoed weg? Gebruik DROP TABLE.

DELETE: rijen verwijderen

DELETE is een opdracht voor het wijzigen van gegevens (DML). Het verwijdert rijen één voor één, activeert triggers op rijniveau en kan worden gefilterd. We hebben een kleine tabel opgezet met een audittrigger:

CREATE TABLE logs (id INTEGER PRIMARY KEY AUTOINCREMENT, level TEXT);
INSERT INTO logs (level) VALUES ('INFO'),('ERROR'),('INFO'),('DEBUG');

CREATE TABLE audit (msg TEXT);
CREATE TRIGGER logs_delete AFTER DELETE ON logs
BEGIN INSERT INTO audit VALUES ('deleted ' || OLD.id); END;

DELETE FROM logs WHERE level = 'DEBUG';

SELECT id, level FROM logs;  -- (1,'INFO'), (2,'ERROR'), (3,'INFO')
SELECT * FROM audit;         -- 'deleted 4'

Alleen de overeenkomende rij ging weg en de trigger registreerde dit. Door DELETE FROM logs uit te voeren zonder een WHERE-clausule werden de andere drie rijen verwijderd, en de audittabel bevatte toen vier vermeldingen, één per verwijderde rij.

DELETE reset de tellers voor automatisch verhogen niet. Nadat we de tabel hadden geleegd, ontving onze volgende insert id 5, niet 1. In SQLite komt dit door AUTOINCREMENT. MySQL InnoDB- en SQL Server-identiteitskolommen gaan ook verder waar ze waren na een DELETE.

TRUNCATE: maak de tabel leeg

TRUNCATE TABLE logs; verwijdert elke rij in één keer. Er kan geen WHERE-clausule in staan. In plaats van rijen één voor één te verwijderen, geeft de database doorgaans de gegevenspagina’s van de tabel vrij. Daarom is dit bij grote tabellen veel sneller.

Toen we het in SQLite uitvoerden, kregen we een syntaxisfout, omdat SQLite de opdracht niet heeft:

TRUNCATE TABLE logs;
-- near "TRUNCATE": syntax error

Gebruik in SQLite DELETE FROM logs; zonder WHERE-clausule. De documentatie van SQLite beschrijft een “truncate-optimalisatie” die dit snel maakt als de tabel geen triggers heeft. Onze tabel had een trigger, dus de verwijdering werd rij voor rij uitgevoerd en voor elke rij geactiveerd.

In databases die TRUNCATE ondersteunen, volgens hun documentatie:

• Normaal gesproken worden er geen DELETE-triggers op rijniveau geactiveerd.
• Meestal worden identiteits- of auto-increment-tellers opnieuw ingesteld, bijvoorbeeld in MySQL en SQL Server. In PostgreSQL voeg je RESTART IDENTITY toe.
• Het heeft op de meeste systemen sterkere machtigingen nodig dan DELETE.

DROP: verwijder de tafel zelf

DROP TABLE is een schema-opdracht (DDL). Het verwijdert de gegevens, de tabeldefinitie, de indexen, triggers en beperkingen. Alles dat naar de tabel verwijst, zoals views of externe sleutels, wordt ook beïnvloed.

DROP TABLE logs;
SELECT * FROM logs;
-- no such table: logs

DROP TABLE IF EXISTS logs;  -- no error, even though it is gone

IF EXISTS is handig bij installatiescripts die meerdere keren kunnen worden uitgevoerd. Weergaven van een verwijderde tabel kunnen kapot gaan, zoals we hebben laten zien in SQL-weergaven uitgelegd.

Vergelijkingstabel

Kun je terugrollen?

Dit is waar veel artikelen te simpel zijn. Het eerlijke antwoord is dat het afhangt van de database.

VERWIJDEREN kan overal binnen een transactie worden teruggedraaid. Wij hebben het getest:

BEGIN;
DELETE FROM logs;
SELECT COUNT(*) FROM logs;  -- 0
ROLLBACK;
SELECT COUNT(*) FROM logs;  -- 3

DROP is ook teruggedraaid in SQLite, dat transactionele DDL ondersteunt:

BEGIN;
DROP TABLE logs;
ROLLBACK;
SELECT COUNT(*) FROM logs;  -- 1 (the table is back)

Voor TRUNCATE en DROP in andere databases, volgens hun documentatie:

Dus “TRUNCATE kan nooit worden teruggedraaid” is verkeerd voor PostgreSQL en SQL Server, en “het kan altijd worden teruggedraaid” is verkeerd voor MySQL. Buiten een transactie kan geen enkele transactie ongedaan worden gemaakt zonder een back-up.

Waarom TRUNCATE snel is

DELETE registreert elke verwijderde rij, zodat de wijziging ongedaan kan worden gemaakt en kan worden gerepliceerd, activeert triggers en controleert beperkingen rij voor rij. Op een tafel met miljoenen rijen levert dat veel werk en logvolume op. TRUNCATE maakt de opslag doorgaans in één handeling ongedaan, waardoor er veel minder wordt geregistreerd. Als u een grote subset wilt verwijderen in plaats van alles, verwijdert u deze in batches, zodat u lange vergrendelingen vermijdt. We behandelen de vergrendelings- en querykosten in SQL-queryoptimalisatie.

Externe sleutels en machtigingen

• Buitenlandse sleutels. SQL Server weigert een tabel TRUNCATE waarnaar wordt verwezen door een externe sleutel. PostgreSQL vereist dat u ook de verwijzende tabellen inkort, of CASCADE gebruikt. Een DELETE controleert in plaats daarvan elke rij op de beperkingen.
• Rechten. DELETE heeft verwijderrechten nodig. TRUNCATE heeft vaak ALTER of een speciaal TRUNCATE-recht nodig, en DROP heeft eigendom of ALTER op het schema nodig.
• Replicatie en audit. Als andere systemen afhankelijk zijn van wijzigingslogboeken of triggers op rijniveau, kan TRUNCATE deze overslaan. Neem eerst contact op met de eigenaar van deze systemen.

Welke te gebruiken

Voor testgegevens is TRUNCATE populair omdat het ID’s opnieuw instelt, waardoor tests herhaalbaar worden. Elke test omsluiten in een transactie die wordt teruggedraaid is een andere optie, die wordt uitgelegd in SQL-transacties en ACID.

Veiligheidscontrolelijst

1. Bevestig met welke database en omgeving u bent verbonden.
2. Voer eerst de WHERE-clausule uit als SELECT.
3. Verpak de wijziging in een transactie waar uw database dit ondersteunt.
4. Zorg voor een recente, geteste back-up voordat TRUNCATE of DROP in productie gaat.
5. Plaats DROP-instructies in beoordeelde migratiescripts, nooit in ad-hocconsoles.
6. Controleer wat er van een tabel afhangt, zoals views, refererende sleutels en rapporten, voordat u deze verwijdert.

Veilig grote hoeveelheden gegevens verwijderen

Het verwijderen van miljoenen rijen met één DELETE kan de tabel lange tijd vergrendelen, het transactielogboek vullen en de replicatie vertragen. Een gebruikelijk patroon is om in kleine batches te verwijderen en te herhalen totdat er niets meer over is:

-- SQL Server style (not run for this article)
DELETE TOP (5000) FROM logs WHERE created_at < '2026-01-01';

-- PostgreSQL style (not run for this article)
DELETE FROM logs
WHERE id IN (SELECT id FROM logs WHERE created_at < '2026-01-01' LIMIT 5000);

Voer de instructie in een lus uit, met een korte pauze tussen batches, totdat er nul rijen worden gerapporteerd. Elke batch is een kleine transactie, dus de vergrendelingen zijn kort en andere zoekopdrachten blijven actief. Een index op de filterkolom, hier created_at, houdt elke batch snel.

Voor gegevens die op basis van de datum groeien, zoals logboeken en gebeurtenissen, is tabelpartitionering zelfs nog beter. Elke maand bevindt zich op zijn eigen partitie, en het verwijderen van oude gegevens wordt een snelle handeling waarbij één partitie wordt verwijderd of ingekort in plaats van rijen één voor één worden verwijderd.

Zachte verwijderingen

Veel applicaties verwijderen nooit fysiek bedrijfsgegevens. Ze voegen een kolom toe zoals deleted_at en stellen deze in plaats daarvan in, en zoekopdrachten filteren vervolgens op deleted_at IS NULL. Zachte verwijderingen maken herstel eenvoudig en houden de geschiedenis bij voor audits, maar elke zoekopdracht moet het filter onthouden, wat goed is voor een weergave. Er is ook zorg nodig voor unieke beperkingen, omdat een zacht verwijderde rij nog steeds zijn waarde behoudt. Privacywetgeving kan vereisen dat persoonlijke gegevens na een bepaalde periode daadwerkelijk worden verwijderd. Zachte verwijderingen zijn dus geen vervanging voor een bewaarbeleid.

Tot slot

DELETE is het precieze, zorgvuldige hulpmiddel. TRUNCATE is de snelle resetknop en DROP verwijdert de tabel volledig. Het grootste risico is dat de ene database zich als de andere gedraagt, vooral als het om rollback gaat. Test bij twijfel eerst het commando op een kopie van de tabel in uw eigen database.

Veelgestelde vragen

Blijf leren