SQL ON DELETE CASCADE : Supprimer automatiquement les données dépendantes

La suppression de données dans SQL ne consiste pas seulement à supprimer une seule ligne. Dans une base de données relationnelle, les tables sont souvent reliées entre elles, et une suppression dans une table peut avoir des répercussions sur d'autres tables. Pour gérer cela en toute sécurité, SQL utilise des contraintes de clé étrangère afin de définir comment les données associées doivent se comporter lorsque des lignes sont supprimées ou mises à jour.

L'une des options de suppression les plus efficaces est la contrainte SQL « ON DELETE CASCADE ». Au lieu de bloquer une suppression ou de laisser des enregistrements orphelins, cette règle indique à la base de données de supprimer automatiquement toutes les lignes enfants dépendantes lorsqu'une ligne parent référencée est supprimée.

ON DELETE CASCADE Dans ce tutoriel, je vais vous expliquer le fonctionnement de la contrainte ON DELETE CASCADE, les raisons pour lesquelles les bases de données prennent en charge les suppressions en cascade automatiques et les situations dans lesquelles cette option est préférable à des règles plus restrictivestelles que ON DELETE RESTRICT.  Sivous 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 diriger vers le cours SQL intermédiaire. 

Qu'est-ce que ON DELETE CASCADE en SQL ?

Afin de comprendre le fonctionnement de la contrainte de clé étrangère « ON DELETE CASCADE », permettez-moi d'abord d'expliquer l'intégrité référentielle et la clause « CASCADE ».

Intégrité référentielle et clés étrangères

Dans les bases de données relationnelles, les tables sont reliées par des clés étrangères, qui associent une colonne d'une table (la table enfant) à une clé primaire d'une autre table (la table parent). Les clés étrangères garantissent l'intégrité référentielle afin de s'assurer qu'une ligne enfant pointe toujours vers une ligne parent valide. 

Sans cette mesure, les bases de données pourraient facilement se retrouver avec des enregistrements orphelins, où les lignes font référence à des données qui n'existent plus. Par conséquent, des règles de suppression sont mises en place afin d'indiquer à la base de données comment traiter ces éléments orphelins avant même qu'ils ne surviennent.

Que signifie réellement CASCADE ?

En SQL, la contrainte « ON DELETE CASCADE » (supprimer tout) indique à la base de données de supprimer automatiquement toutes les lignes enfants faisant référence à la ligne parent supprimée. Il déclenche l'opération de manière récursive à travers les chaînes de clés étrangères, supprimant les données dépendantes en une seule transaction atomique d'.

Par exemple, vous pouvez supprimer l'enregistrement d'un client dans la base de données, ce qui entraînera la suppression de ses commandes, puis la suppression des articles commandés en une seule requête.

Fonctionnement pratique de ON DELETE CASCADE

Maintenant que vous comprenez le fonctionnement de CASCADE, examinons comment la base de données applique cette logique.

Relations entre les tables parent et enfant

Comme vous le savez désormais, les relations entre bases de données comportent une table qui agit en tant que parent, contenantla clé primaire, tandis qu'une autre agit en tant qu'enfant, stockant cette clé en tant que clé étrangère. Lorsque vous interrogez une instruction DELETE sur la table parent, le moteur de base de données vérifie d'abord les contraintes de clé étrangère afin d'identifier tous les dépendantsnts grâce à l'indexation pour plusde rapidité.

Que se produit-il lorsqu'une ligne parent est supprimée ?

Lorsque vous exécutez une commande d'DELETE sur une ligne parente, la base de données suspend l'exécution finale afin de vérifier ses règles de clé étrangère dans l'ordre suivant :

• Identification : Le moteur examine l'index de clé étrangère pour trouver toutes les lignes des tables enfants qui correspondent à l'ID en cours de suppression.
• Exécution : Avant (ou pendant) la suppression de la ligne parent, le moteur supprime les lignes enfants identifiées.
• Validation : La base de données garantit qu'aucune ligne orpheline ne subsiste. Si toutes les suppressions sont effectuées avec succès, la transaction est finalisée.

Veuillez noter que le processus ci-dessus ne se produira que si vous avez défini la contrainte ON DELETE CASCADE, comme nous le verrons plus loin dans cet article.

SUPPRESSION EN CASCADE vs. Autres règles de suppression

En SQL, il existe différentes règles d'DELETE s qui indiquent à la base de données comment traiter les données liées à différentes tables. Dans cette section, nous comparerons l'ON DELETE CASCADE e avec ces contraintes.

CASCADE contre RESTRICT

D'après l'explication ci-dessus, nous constatons que l'CASCADE supprime automatiquement toutes les lignes enfants dépendantes lorsqu'une ligne parent est supprimée, garantissant ainsi qu'aucun enregistrement orphelin ne subsiste.

Cependant, la contrainte SQL « RESTRICT » bloque complètement l'opération de suppression si des lignes enfants font encore référence à la ligne parent. Cette opération garantit qu'aucune perte de données accidentelle ne se produira. Dans l'exemple ci-dessous, la contrainte « RESTRICT » empêche la suppression d'un client s'il existe des commandes qui font référence à ce client.

-- 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
);Propulsé par

CASCADE contre SET NULL et SET DEFAULT

Alors que CASCADE supprime entièrement les lignes dépendantes, SET NULL et SET DEFAULT conservent les lignes enfants de la manière suivante :

Lorsque vous définissez votre contrainte comme « ON DELETE SET NULL », la ligne parent est supprimée, mais les valeurs de clé étrangère dans la table enfant sont définies sur « NULL ».

Dans l'exemple ci-dessous, la suppression d'un client n'entraîne pas la suppression des commandes associées. Au lieu de cela, la colonne « customer_id » (Numéro de série) du tableau « orders » (Numéros de série) est automatiquement définie sur « NULL ». Cela permet de conserver les enregistrements de commande tout en supprimant le lien avec le client supprimé.

-- 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
);Propulsé par

De même, avec l'option « SET DEFAULT » (Supprimer le client), la suppression d'un client n'entraîne pas la suppression des commandes associées. Au lieu de cela, l'customer_id e dans le tableau orders est réinitialisée à sa valeur par défaut définie. Cela permet de conserver les enregistrements de commande intacts tout en leur attribuant un espace réservé prédéfini.

-- 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
);Propulsé par

Création de clés étrangères avec ON DELETE CASCADE

Pour utiliser la contrainte d'ON DELETE RESTRICT e dans SQL, il est nécessaire de la spécifier lors de l'établissement de la relation de clé étrangère entre les tables parent et enfant.

CASCADE dans les schémas de table

Vous pouvez définir la règle d'ON DELETE CASCADE dans SQL soit lors de la création de tables, soit en modifiant des tables existantes.

L'exemple ci-dessous illustre comment appliquer la contrainte de clé étrangère SQL « ON DELETE CASCADE » lors de la définition d'un nouveau tableau. Avec CASCADE, la suppression d'un service entraîne automatiquement la suppression de tous les employés associés à ce service.

-- 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
);Propulsé par

Si la table existe déjà, vous pouvez ajouter une contrainte de clé étrangère à l'aide de la commande ` ON DELETE CASCADE`. Par conséquent, si vous supprimez un service, tous les dossiers des employés associés seront automatiquement supprimés.

-- 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 deletedPropulsé par

Chaînes en cascade et suppressions à plusieurs niveaux

L'une des fonctionnalités avancées d'CASCADE, est que les suppressions ne se limitent pas à une seule relation. Si une table enfant est également la table mère d'une autre table, la suppression peut se propager à plusieurs niveaux, appelés chaînes en cascade. Par exemple, la suppression d'un utilisateur peut entraîner la suppression de commandes, qui à leur tour suppriment des articles de commande, ce qui peut entraîner la suppression de lignes d'audit associées.

Il est donc essentiel de documenter et d'examiner attentivement les schémas qui utilisent CASCADE. Étant donné qu'une seule instruction de suppression peut affecter plusieurs tables, il est important de bien comprendre la chaîne de dépendance complète avant d'activer le comportement en cascade.

Cas d'utilisation courants pour ON DELETE CASCADE

La contrainte d'ON DELETE CASCADE e dans SQL est importante dans les scénarios où les données de la table enfant n'ont aucune valeur sans l'enregistrement parent. Examinons son application dans la gestion de bases de données en situation réelle.

Nettoyage automatique des données dépendantes

Vous pouvez utiliser ON DELETE CASCADE pour nettoyer les données lorsque le nettoyage manuel s'avère inefficace. Il s'agit notamment de tables telles que les journaux, les enregistrements historiques, les pistes d'audit ou les tables de jointure plusieurs-à-plusieurs qui existent souvent uniquement pour prendre en charge un enregistrement parent. Par exemple :

• La suppression d'un utilisateur devrait supprimer son historique de connexion.
• La suppression d'une commande devrait supprimer ses articles.
• La suppression d'un produit devrait supprimer les entrées dans une table de mappage produit-catégorie.

Simplification de la gestion du cycle de vie des données

Vous pouvez également utiliser CASCADE pour déplacer les règles du cycle de vie des données vers la base de données. Au lieu de dépendre du code de l'application pour trouver les enregistrements associés, les supprimer dans le bon ordre et gérer les cas particuliers et les échecs, la base de données applique ces règles de manière cohérente et atomique.

De cette manière, CASCADE garantit que, quelle que soit la manière dont la suppression est déclenchée, que ce soit via une application, un script ou une requête administrative, le résultat est toujours cohérent.

Risques et inconvénients de ON DELETE CASCADE

Bien que la contrainte d'ON DELETE CASCADE e puisse être pratique, elle peut présenter des risques si elle est mal utilisée. Il est essentiel de comprendre les pièges courants pour concevoir des contraintes d'CASCADE s efficaces.

Suppressions massives accidentelles

Le risque le plus courant lié à l'CASCADE, c'est la suppression accidentelle de données à grande échelle. Une seule instruction « DELETE » sur une table parente peut supprimer de manière silencieuse des centaines, voire des milliers de lignes associées dans plusieurs tables. Cette action peut entraîner une perte de données inattendue qui peut être difficile à récupérer si vous n'avez pas bien compris la relation entre les tables. 

Il est recommandé de toujours s'assurer que la relation entre les clés étrangères dans les tables parentes et enfants est traçable. Si vous définissez correctement le schéma, vous saurez quelles tables seraient affectées par une seule suppression.

Dépendances cachées dans les schémas complexes

À mesure que les bases de données s'enrichissent, les tables accumulent des clés étrangères supplémentaires, et la documentation peut se désynchroniser par rapport à la réalité. Le défi se présente lorsque l'CASCADE est mise en œuvre dans de tels environnements et que les suppressions peuvent se propager à travers des chaînes de dépendances profondément imbriquées dont les développeurs ou les administrateurs n'ont pas connaissance.

D'après mon expérience, je vous recommande d'associer l'CASCADE e à une documentation claire du schéma et à une révision régulière des relations entre les clés étrangères.

Si vous concevez ou gérez régulièrement des bases de données dans le cadre de votre travail,nous vous invitons à explorernotrecursus SQL Server pour les administrateurs de bases de données. 

Remarques et comportement spécifiques à la base de données

Bien que le concept d'ON DELETE CASCADE s soit standard dans toutes les bases de données relationnelles, chaque moteur gère l'exécution sous-jacente avec de légères différences.

Comportement de PostgreSQL, MySQL et SQL Server

PostgreSQL, MySQL et SQL Serverprennent tousen charge l'ON DELETE CASCADE et l'implémentent de la même manière, à l'exception des cas suivants :

• MySQL :CASCADE fonctionne efficacement avec InnoDB, mais évite les cascades circulaires.

• PostgreSQL: Le plus flexible et permissif avec les chaînes de suppression en cascade

• SQL Server : Plus sécurisé mais plus restrictif en raison des règles relatives aux chemins en cascade multiples

Portée des transactions et sécurité des annulations

Les suppressions en cascade sont exécutées dans le cadre de la même transaction que l'instruction de suppression initiale. Si la base de données rencontre une erreur lors de la suppression d'une ligne enfant, l'opération entière échoue. La ligne parent n'est pas supprimée et toutes les lignes enfants déjà supprimées sont restaurées.

Ce comportement transactionnel constitue un filet de sécurité important. Il vous permet de tester, de vérifier ou d'annuler les suppressions en cascade avant de les valider.

Choisir la bonne stratégie ON DELETE

À présent, vous comprenez que le choix de la stratégie de suppression appropriée dépend de la manière dont vos données sont reliées entre elles et de la manière dont vous souhaitez qu'elles évoluent au fil du temps. Veuillez prendre en considération les directives suivantes afin de faire le choix approprié en matière d'DELETE s pour votre schéma.

Quand CASCADE est le choix approprié

Il est recommandé d'utiliser la contrainte « ON DELETE CASCADE » lorsque les données enfants dépendent entièrement des données parents pour leur signification ou leur existence. Par exemple, vous pouvez l'utiliser dans les cas suivants :

• Joindre des tables dans des relations plusieurs-à-plusieurs
• Données temporaires ou dérivées, telles que les journaux ou les entrées d'historique

Dans les cas susmentionnés, le nettoyage automatique garantit la cohérence tout en réduisant la logique de suppression manuelle requise dans la couche applicative.

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

Quand éviter CASCADE

Cependant, il est recommandé d'éviter la contrainte d'ON DELETE CASCADE e dans votre schéma si les données enfants ont une valeur indépendante ou doivent être conservées à des fins juridiques, d'audit ou d'analyse. Par exemple, vous ne devez pas utiliser CASCADE dans les cas suivants :

• Documents financiers et factures
• Journaux d'audit et données de conformité, telles que les données relatives aux soins de santé.

Dans de tels cas, je recommande d'utiliser des règles restrictives, telles que RESTRICT, SET NULL ou des suppressions gérées par l'application, afin d'offrir un meilleur contrôle et une meilleure visibilité.

Conclusion

La contrainte d'intégrité référentielle SQL est un outil d'automatisation puissant qui préserve l'ON DELETE CASCADE référentielle en supprimant automatiquement les données dépendantes lorsqu'une ligne parente est supprimée. Utilisé de manière appropriée, il simplifie le nettoyage, réduit la logique de suppression manuelle et garantit un comportement cohérent entre les applications et les scripts. Afin d'éviter toute suppression involontaire, veuillez faire preuve de prudence lorsque vous utilisez CASCADE si vous ne comprenez pas parfaitement les relations au sein du schéma de la base de données.

Je vous recommande de suivre notre cours 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 notre cursus professionnel d'ingénieur de données associé en SQL afin d'acquérir les bases de l'ingénierie des données et du stockage des données.