SQL SUPPRIMER : Comment supprimer des données en toute sécurité

La suppression de données est une opération potentiellement risquée dans SQL. Une seule commande mal rédigée peut perturber les relations entre les tableaux ou même effacer l'ensemble des données.

Dans ce tutoriel, je vais vous expliquer le fonctionnement de l'instruction ` DELETE `, comment l'utiliser correctement et comment éviter les erreurs courantes, telles que la suppression d'un ensemble de données complet, comme je viens de le mentionner. 

Si vous débutez avec SQL, nous vous recommandons de commencer par notre cours Introduction à SQL. Si vous avez déjà une certaine expérience, vous pouvez vous inscrire à notre cours SQL intermédiaire. 

Quelle est la fonction de DELETE en SQL ?

Avant d'examiner la syntaxe, il est important de comprendre ce que fait réellement l'instruction ` DELETE `. Voici les différentes distinctions relatives à la suppression de données dans SQL.

SQL DELETE par rapport à la suppression conceptuelle de lignes

L'instruction « DELETE » supprime une ou plusieurs lignes d'un tableau tout en conservant la structure du tableau. Par conséquent, le schéma de la table, qui comprend les noms des colonnes, les types de données, les index et les contraintes, reste inchangé. Après la suppression, la table est toujours présente et prête à recevoir de nouvelles données.

Considérez cela comme effacer des entrées d'un registre, et non comme déchirer les pages. Le schéma reste inchangé ; seuls les enregistrements sélectionnés sont supprimés.

SQL DELETE par rapport à DROP vs. TRONQUER

Bien que DELETE, DROP et TRUNCATE suppriment tous des données sous une forme ou une autre, ilsont des objectifs très différents. Le tableau suivant résume l'utilisation de chaque instruction :

Syntaxe de base de la commande DELETE en SQL

Maintenant que vous avez compris le fonctionnement de l'instruction ` DELETE `, examinons sa syntaxe et comment l'appliquer. 

Suppression de lignes spécifiques avec WHERE

La clause ` WHERE ` définit la portée d'une opération ` DELETE `. La clause « WHERE » est toujours requise, car elle indique précisément à la base de données quelles lignes doivent être supprimées.

-- Basic DELETE syntax
DELETE FROM table_name
WHERE condition;

Suppression de toutes les lignes d'une table

Si vous omettez la clause d'WHERE dans l'instruction DELETE, SQL suppose que vous souhaitez cibler chaque ligne de la table. Si vous effectuez cette opération par inadvertance, le moteur de base de données examinera chaque enregistrement et le supprimera. Pour chaque ligne supprimée, une entrée est enregistrée dans le journal des transactions. Si vous avez des millions de lignes, cela peut être très lent et entraîner un gonflement de vos fichiers journaux.

Pratiques sécurisées pour SQL DELETE

Étant donné qu'une opération d'DELETE ion peut être irréversible, la mise en œuvre de pratiques de suppression sécurisées vous aidera à ralentir le processus, à vérifier votre intention et à planifier la manière de récupérer les données si/lorsque quelque chose ne se passe pas comme prévu.

Prévisualisation des lignes avant suppression

Avant d'exécuter une instruction ` DELETE `, veuillez toujours prévisualiser les lignes que vous vous apprêtez à supprimer à l'aide d'une instruction ` SELECT ` avec la même condition ` WHERE ` que celle que vous prévoyez d'utiliser dans votre ` DELETE`. Si l'instruction « SELECT » renvoie un nombre excessif de lignes ou des lignes incorrectes, il est possible de corriger la condition avant que des dommages ne surviennent. 

Par exemple, la requête ci-dessous recherche les lignes contenant des commandes annulées avant le 2023-01-01, puis utilise les mêmes filtres pour supprimer ces enregistrements.

-- Step 1: Preview the targets
SELECT * FROM Orders 
WHERE Status = 'Cancelled' AND OrderDate < '2023-01-01';

-- Step 2: Once you've verified the list, convert to DELETE
DELETE FROM Orders 
WHERE Status = 'Cancelled' AND OrderDate < '2023-01-01';

Utilisation des transactions et du rollback

Si vous intégrez l'opération d'DELETE ion dans une transaction, cela vous permet d'exécuter une commande, de déterminer le nombre de lignes affectées, puis de décider de rendre la modification permanente ou de l'annuler entièrement. Vous pouvez ensuite utiliser l'outil de restauration des données ( ROLLBACK ) pour rétablir les données dans leur état antérieur.

La requête ci-dessous supprime temporairement les lignes concernées, puis les restaure, vous permettant ainsi de confirmer l'impact sans perdre de données.

BEGIN TRANSACTION;

DELETE FROM Users
WHERE LastLogin < '2020-01-01';

-- Inspect the affected row count
ROLLBACK;

Vous pouvez ensuite utiliser la requête suivante pour supprimer définitivement les utilisateurs qui se sont connectés pour la dernière fois avant le 1er janvier 2020.

BEGIN TRANSACTION;

DELETE FROM Users
WHERE LastLogin < '2020-01-01';

-- Confirm deletion of the required rows
COMMIT;

Veuillez toutefois noter que l'ROLLBACK n n'est possible que si vous n'avez pas encore validé la transaction.

SQL DELETE avec jointures et sous-requêtes

Dans les bases de données réelles, les données sont rarement isolées. Dans la plupart des cas, il peut être nécessaire de supprimer des lignes en fonction des valeurs stockées dans d'autres tables, des comptes expirés, des enregistrements orphelins ou des entités associées qui ne sont plus pertinentes.

Suppression de lignes en fonction d'une autre table

L'utilisation de sous-requêtes constitue l'approche la plus portable et la plus largement prise en charge pour supprimer des lignes en fonction d'un autre tableau. Ils sont compatibles avec presque toutes les versions SQL. Par exemple, la requête ci-dessous supprime les utilisateurs inactifs de la table Users qui ont désactivé leur compte dans la table Accounts.

-- Delete users whose accounts have been deactivated
DELETE FROM Users
WHERE AccountId IN (
    SELECT AccountId
    FROM Accounts
    WHERE Status = 'DEACTIVATED'
);

Il est également possible d'utiliser les jointures pour supprimer des lignes dans une base de données, mais la syntaxe varie selon la base de données, comme nous le verrons dans la section suivante.

Syntaxe DELETE spécifique à la base de données

Bien que le concept de l'instruction ` DELETE ` soit identique en SQL, différentes bases de données utilisent des syntaxes différentes lors de l'utilisation de ` JOIN ` pour supprimer des lignes d'un tableau en fonction d'un autre.

SQL Server positionne la table cible après l'instruction ` DELETE `, puis effectue une jointure dans ` FROM`. Dans l'exemple ci-dessous, l'alias après DELETE (u) indique quelle table est supprimée.

-- SQL Server: Delete users linked to deactivated accounts
DELETE u
FROM Users u
JOIN Accounts a
  ON u.AccountId = a.AccountId
WHERE a.Status = 'DEACTIVATED';

Dans PostgreSQL, l'interface de gestion des objets ( USING ) agit comme une interface de gestion des objets ( JOIN). Seules les lignes de la table cible (Users) sont supprimées.

-- PostgreSQL: Delete users linked to deactivated accounts
DELETE FROM Users
USING Accounts
WHERE Users.AccountId = Accounts.AccountId
  AND Accounts.Status = 'DEACTIVATED';

MySQL autorise les suppressions multi-tables, mais nécessite une désignation explicite des tables. Veuillez placer la table à supprimer avant la clause ` FROM `.

-- MySQL: Delete users linked to deactivated accounts
DELETE u
FROM Users u
JOIN Accounts a
  ON u.AccountId = a.AccountId
WHERE a.Status = 'DEACTIVATED';

Je vous recommande de suivre notre cours « Joindre des données dans SQL » pour découvrirles différents types de jointures dans SQL et apprendre à travailler avec différents tableaux liés dans la base de données.

SQL DELETE et intégrité référentielle

Comme nous le savons maintenant, les tables existent rarement de manière isolée dans une base de données relationnelle. Ils sont fréquemment reliés entre eux à l'aide de clés étrangères. En raison de ces liens, la suppression d'une ligne dans un tableau peut entraîner des conséquences imprévues.

Erreurs de clés étrangères et de contraintes

Lorsqu'une table est référencéepar une clé étrangère, la base de données applique des règles concernant ce qui se produit lorsque vous essayez de supprimer une ligne parent. Par défaut, la plupart des bases de données empêchent la suppression si des lignes associées existent toujours dans une table enfant.

Par exemple, si vous disposez d'une table Orders qui fait référence à la table Customers, la suppression d'un client qui a encore des commandes échouera en raison d'une erreur de contrainte. Cela permet d'éviter que la base de données ne contienne des enregistrements orphelins qui n'ont plus de parent valide.

Suppressions en cascade

Si vous souhaitez supprimer automatiquement les données associées, il est nécessaire de définir explicitement une règle en cascade qui indique à la base de données comment traiter les données associées. La méthode ` ON DELETE CASCADE ` indique à la base de données de supprimer automatiquement les lignes enfants associées lorsqu'une ligne parent est supprimée.

Par exemple, la requête ci-dessous demande à la base de données de supprimer automatiquement toutes les lignes du tableau Orders qui font référence à un client lorsque ce client est supprimé du tableau Customers.

-- Parent table
CREATE TABLE Customers (
    CustomerId INT PRIMARY KEY,
    Name VARCHAR(100)
);

-- Child table with a cascading foreign key
CREATE TABLE Orders (
    OrderId INT PRIMARY KEY,
    CustomerId INT,
    OrderDate DATE,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (CustomerId)
        REFERENCES Customers(CustomerId)
        ON DELETE CASCADE
);

L'avantage de cette approche est qu'elle permet de maintenir la base de données propre sans nécessiter de multiples instructions d'DELETE manuelles. Cependant, il est important d'utiliser cette instruction avec précaution, car une seule suppression peut déclencher une réaction en chaîne sur plusieurs tables, supprimant ainsi beaucoup plus de données que prévu. Par exemple, vous pourriez supprimer une ligne dans un tableau Departments et effacer accidentellement 500 lignes dans un tableau Employees.

Considérations relatives aux performances lors de la suppression de données

Lorsque vous utilisez l'instruction ` DELETE `, vous remarquerez que la suppression de quelques lignes est instantanée, mais qu'elle est lente si vous avez des millions de lignes. Veuillez prendre en considération les meilleures pratiques suivantes pour supprimer des enregistrements en toute sécurité :

Supprimer par opposition à TRUNCATE pour les grandes tables

Comme nous l'avons vu précédemment, les commandes ` DELETE ` et ` TRUNCATE ` suppriment toutes deux les enregistrements de la manière suivante :

• DELETE: Il parcourt chaque ligne, vérifie si elle répond aux critères, la supprime et enregistre la modification dans le journal des transactions. Ceci est gourmand en ressources et lent pour les ensembles de données volumineux. Il permet le filtrage à l'aide d'une clause « WHERE » et prend en charge la restauration.

• TRUNCATE: Supprime toutes les lignes en une seule foise avec un enregistrement minimal. Il est considérablement plus rapide, mais ne peut pas être filtré, ne peut généralement pas être annulé et est bloqué par des contraintes de clé étrangère.

Suppressions groupées et opérations de longue durée

Si vous supprimez des millions de lignes en une seule instruction, cela pourrait ralentir considérablement l'ensemble de votre application et même entraîner le plantage de votre base de données si cela n'est pas géré correctement. Afin de minimiser ce risque, veuillez toujours supprimer les ensembles de données volumineux par lots.

Les suppressions par lots permettent de supprimer un nombre limité de lignes à la fois, par exemple quelques milliers par opération. Cela permet à la base de données de rester réactive et de faciliter le suivi des progrès. Si un problème survient, vous pouvez interrompre le processus sans avoir à annuler une transaction importante.

Erreurs courantes avec SQL DELETE

Voici les erreurs courantes à éviter lorsque vous utilisez l'instruction DELETE pour supprimer des lignes dans vos tables :

Oublier la clause WHERE

Oublier la clause d'WHERE est l'erreur la plus courante lors de l'utilisation de DELETE. Sans cela, l'instruction supprime toutes les lignes du tableau. Veuillez toujours vous assurer d'utiliser la clause « WHERE » pour spécifier les lignes à supprimer. À titre préventif, veuillez adopter l'habitude dont nous avons discuté précédemment, à savoir utiliser la commande « SELECT-before » avant «DELETE » afin de visualiser les lignes avant leur suppression.

En supposant que DELETE soit réversible

Une autre idée fausse courante est que les données supprimées peuvent toujours être récupérées. Veuillez noter que, par défaut, l'instruction « DELETE », une fois validée, marque les données comme physiquement supprimées du disque, à moins que vous ne disposiez de sauvegardes, de répliques ou de journaux d'audit.

Vous pouvez « annuler » une commande d'DELETE uniquement si :

• Il est exécuté dans le cadre d'une transaction.
• La transaction n'a pas été confirmée.
• La base de données et le moteur de stockage prennent en charge la restauration.

SQL DELETE dans les environnements de production

Dans les systèmes de production, l'opération d'DELETE e concerne davantage le contrôle, la visibilité et la responsabilité que la syntaxe. Dans de tels environnements, l'objectif n'est pas seulement de supprimer des données, mais de le faire de manière sécurisée et réfléchie.

Contrôle d'accès et autorisations

Dans un contexte professionnel, la capacité à supprimer des données doit respecter le principe du moindre privilège. Tous les comptes d'utilisateurs ou de services d'application ne devraient pas nécessairement disposer des autorisations d' DELETE. 

La plupart des utilisateurs « en lecture seule » ou des outils de reporting devraient se voir explicitement refuser ce droit. La plupart des entreprises exigent généralement un « examen par les pairs », dans le cadre duquel une deuxième personne doit vérifier la clause d'WHERE e d'un script de suppression manuelle avant qu'il ne soit exécuté en production.

Je vous recommande de suivre le cursus SQL Server pour les administrateurs de bases de données sivous concevez ou gérez régulièrement des bases de données dans le cadre de votre travail. 

Audit et suppressions temporaires

De nombreux systèmes de production évitent les suppressions définitives en raison des risques qu'elles comportent. Au lieu de cela, ils utilisent des suppressions temporaires, où une ligne est marquée comme supprimée. Par exemple, vous pouvez utiliser un horodatage « deleted_at » ou un indicateur « is_deleted » plutôt que de supprimer physiquement un enregistrement.

Les suppressions temporaires facilitent la récupération, permettent l'audit et préservent les données historiques à des fins de débogage et de conformité.

Conclusion

Le point le plus important à retenir est le suivant : Veuillez toujours définir une clause d'WHERE claire, prévisualiser les lignes concernées à l'aide de la commande ` SELECT` et utiliser des transactions dans la mesure du possible. Dans les systèmes de production, toute suppression doit être intentionnelle et vérifiable. Traitez l'DELETE avec le même soin que vous accorderiez à une modification de schéma ou à un déploiement, et elle restera un outil utile plutôt qu'une erreur coûteuse.

Je vous recommande de suivre notrecours sur la conception de bases de données, où vous apprendrez à créer et à gérer des bases de données, ainsi qu'à sélectionner le système de gestion de base de données (SGBD) le mieux adapté à vos besoins. Je vous recommande également d'essayer notrecursus professionnel d'ingénieur de données associé en SQLafin d'acquérir les bases de l'ingénierie des données et du stockage des données.