SQL TRUNCATE et DROP TABLE

Leçon 9.3 · Temps de lecture : ~6 min · Exercices pratiques ↓

SQL TRUNCATE supprime toutes les lignes d'une table et garde la table ; DROP TABLE supprime la table elle-même, avec ses données et sa structure. Dans cette leçon, vous apprendrez comment fonctionnent ces deux instructions, en quoi elles diffèrent de DELETE en vitesse, annulation et auto-incrément, et comment les clés étrangères les affectent.

On peut supprimer des données à trois niveaux : certaines lignes (DELETE ... WHERE), toutes les lignes (TRUNCATE) ou la table entière (DROP TABLE). Le premier a été vu dans la leçon 8.3. TRUNCATE et DROP TABLE sont des instructions DDL : elles agissent sur la table dans son ensemble et sont beaucoup plus rapides, mais aussi moins indulgentes.

Les bases d'exemple de SQLtest sont en lecture seule ; les exemples créent leur propre table, vous pouvez donc les exécuter dans le bac à sable sur une base sans données d'exemple.

TRUNCATE : vider la table

TRUNCATE TABLE table_name;

Après TRUNCATE :

• toutes les lignes ont disparu ;
• la table, ses colonnes, ses index et ses contraintes restent ;
• le compteur d'auto-incrément repart de 1.

CREATE TABLE daily_import (
    id INT AUTO_INCREMENT PRIMARY KEY,
    payload VARCHAR(100)
);
INSERT INTO daily_import (payload) VALUES ('a'), ('b'), ('c');

TRUNCATE TABLE daily_import;

INSERT INTO daily_import (payload) VALUES ('d');
SELECT * FROM daily_import;

Résultat : une ligne, id = 1, payload = 'd'. La table a été vidée et son compteur d'id réinitialisé.

DROP TABLE : supprimer la table

DROP TABLE table_name;

Après DROP TABLE, la table n'existe plus : ses données, colonnes, index et contraintes ont disparu, et toute requête qui l'utilise échoue jusqu'à ce qu'elle soit recréée.

DROP TABLE IF EXISTS old_reports;

IF EXISTS ignore l'instruction sans erreur quand la table n'existe pas, ce qui est utile dans les scripts qui recréent des tables.

TRUNCATE vs DELETE vs DROP

La ligne sur l'annulation est la plus importante. Dans MySQL et MariaDB, les instructions DDL valident automatiquement la transaction en cours : un TRUNCATE ou un DROP TABLE par erreur ne peut donc pas être annulé avec ROLLBACK. Seule une sauvegarde peut aider.

Clés étrangères

Les clés étrangères protègent les tables que d'autres tables référencent :

• DROP TABLE échoue si une autre table a une clé étrangère vers elle. Supprimez d'abord la table enfant, ou la contrainte.
• Dans MySQL, TRUNCATE échoue pour une table référencée par une clé étrangère, même si la table enfant est vide. DELETE FROM fonctionne dans ce cas, car il vérifie les lignes réelles.

CREATE TABLE departments (department_id INT PRIMARY KEY);
CREATE TABLE staff (
    staff_id INT PRIMARY KEY,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments (department_id)
);

-- Erreur : Cannot truncate a table referenced in a foreign key constraint
TRUNCATE TABLE departments;

Laquelle utiliser

• Supprimer certaines lignes → DELETE ... WHERE.
• Vider une table que vous allez remplir à nouveau, comme une table intermédiaire d'import → TRUNCATE.
• La table n'est plus nécessaire → DROP TABLE.

Avant un TRUNCATE ou un DROP sur une vraie base, vérifiez le nom de la table et la base à laquelle vous êtes connecté : les deux instructions s'exécutent instantanément et, dans MySQL, sans retour possible.

Points clés :

• TRUNCATE TABLE supprime rapidement toutes les lignes et garde la table ; le compteur d'auto-incrément est réinitialisé.
• DROP TABLE supprime la table avec ses données et sa structure ; IF EXISTS évite une erreur.
• DELETE peut supprimer une partie des lignes et peut être annulé ; TRUNCATE ne peut pas utiliser WHERE.
• Dans MySQL et MariaDB, TRUNCATE et DROP font un COMMIT implicite et ne peuvent pas être annulés.
• Les clés étrangères empêchent de supprimer ou de vider une table référencée.

Questions fréquentes

Quelle est la différence entre TRUNCATE et DELETE ?

DELETE supprime les lignes une par une, peut utiliser WHERE, déclenche les triggers de suppression et peut être annulé. TRUNCATE supprime toutes les lignes d'un coup, est beaucoup plus rapide, réinitialise le compteur d'auto-incrément et, dans MySQL, ne peut pas être annulé.

Quelle est la différence entre TRUNCATE et DROP TABLE ?

TRUNCATE vide la table mais la garde, prête à recevoir de nouvelles lignes. DROP TABLE supprime complètement la table ; pour la réutiliser, il faut la recréer avec CREATE TABLE.

Peut-on annuler un TRUNCATE ?

Dans MySQL et MariaDB, non : il provoque un COMMIT implicite. Dans PostgreSQL et SQL Server, oui, s'il est exécuté dans une transaction.