SQL delete with cascade : modèles de schémas pour une intégrité maîtrisée

Supprimer un client d’une base de données e-commerce entraîne, dans un schéma bien construit, la suppression automatique de ses commandes, de ses adresses et de ses préférences. C’est exactement ce que fait SQL delete with cascade : propager la suppression d’un enregistrement parent vers toutes les tables enfants liées par une clé étrangère. La syntaxe est simple, les effets peuvent être massifs – et les angles morts, réels.

Ce que la cascade ne trace pas par défaut dans MySQL et PostgreSQL

Voici la contrainte que la plupart des tutoriels ignorent : en MySQL avec le moteur InnoDB, les suppressions déclenchées par ON DELETE CASCADE ne déclenchent pas les triggers des tables enfants. Un trigger d’audit sur la table commandes ne se déclenche pas si la ligne est supprimée en cascade depuis la table clients. L’enregistrement disparaît sans trace dans les journaux applicatifs.

Le même angle mort existe pour les outils de capture de changements (CDC) basés sur le binlog MySQL. Les actions de cascade ne sont pas toujours reflétées comme des événements indépendants dans le flux de réplication. Pour un pipeline qui consomme ces changements – Debezium, Kafka Connect, ou tout autre système – les suppressions enfants peuvent passer inaperçues.

PostgreSQL se comporte différemment : les suppressions en cascade y sont journalisées ligne par ligne dans le WAL, ce qui les rend visibles pour les outils de réplication logique. Ce point mérite d’être vérifié explicitement avant de choisir le comportement de la contrainte, selon le SGBD en place.

Schéma de suppression en cascade : deux modèles à choisir selon le contexte

La décision entre cascade déclarative et cascade applicative n’est pas purement technique. Elle dépend du niveau de traçabilité requis et de la volumétrie des tables concernées.

Cascade déclarative (clé étrangère avec ON DELETE CASCADE) : adaptée quand les tables enfants sont des données de cycle de vie du parent – lignes de commande, adresses de livraison, tokens de session. La suppression est atomique, cohérente, et ne nécessite pas de code applicatif. La syntaxe est identique sur les trois grands SGBD :

• MySQL (InnoDB) : FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE – à noter que InnoDB exige un index sur la colonne référencée dans la table parent, mais n’en crée pas automatiquement sur la colonne référençante dans la table enfant.
• PostgreSQL : même syntaxe, mais la documentation PostgreSQL rappelle explicitement que la clé étrangère n’ajoute pas d’index sur la colonne référençante. Sur une table enfant volumineuse, l’absence d’index force un parcours complet de table à chaque suppression du parent – une vérification à faire avant d’activer la cascade en production.
• SQL Server : même syntaxe dans la définition de contrainte, mais SQL Server interdit les cascades circulaires et les chemins de cascade multiples vers la même table, ce qui peut forcer à décomposer le schéma.

Cascade applicative (suppression explicite dans le code ou une procédure stockée) : à privilégier quand on a besoin de journaliser chaque suppression enfant, de conditionner la propagation à une règle métier, ou d’alimenter correctement un flux CDC. La suppression explicite des enfants avant le parent produit des événements SQL distincts, visibles par les triggers et les outils de réplication.

Intégrité après restauration : le problème que la cascade seule ne résout pas

Supprimer des données en cascade dans la base active ne suffit pas lorsque la conformité RGPD est en jeu. Le Comité européen de la protection des données recommande de prévoir comment repérer et réappliquer les effacements après une restauration de sauvegarde, dans les cas où modifier directement la sauvegarde n’est pas adapté.

Concrètement : si on supprime un client et toutes ses données liées, puis que la base est restaurée depuis un backup antérieur à cette suppression, les données réapparaissent. La cascade SQL n’a aucun effet sur ce scénario.

Deux approches complémentaires permettent de gérer ce cas :

• Table de tombstones : une table suppression_log enregistre chaque identifiant supprimé avec un horodatage et un motif. Après chaque restauration, un job compare la base restaurée à cette table et réapplique les suppressions manquantes. Cette table doit elle-même être exclue des backups courants, ou versionnée séparément.
• Soft delete avec colonne d’état : on ne supprime jamais physiquement les données, on les marque deleted_at ou status = 'supprimé'. La cascade n’est plus nécessaire au sens strict, mais il faut s’assurer que toutes les requêtes applicatives filtrent correctement ces lignes – et que les données marquées sont bien anonymisées si l’effacement est requis par une demande RGPD.

Ces deux approches ne s’excluent pas. Un schéma robuste peut combiner une cascade déclarative pour l’intégrité référentielle et une table de tombstones pour la traçabilité post-restauration.

Contraintes de déploiement en production sur tables volumineuses

Ajouter ou modifier une contrainte de clé étrangère avec CASCADE sur une table qui contient des millions de lignes peut provoquer une contention en production. La table est verrouillée le temps de la validation de la contrainte.

PostgreSQL permet d’ajouter la contrainte en deux temps : ADD CONSTRAINT ... NOT VALID puis VALIDATE CONSTRAINT séparément, ce qui limite la durée du verrou. La documentation de développement GitLab décrit des méthodes distinctes pour l’ajout et la validation asynchrones de clés étrangères, notamment pour les tables partitionnées où la gestion des contraintes nécessite de traiter chaque partition individuellement.

Sur MySQL, la solution ALTER TABLE classique pose un verrou exclusif. Des outils comme pt-online-schema-change ou gh-ost permettent de modifier le schéma sans interruption de service, mais nécessitent une validation sur un environnement de préproduction avant toute migration en production.

Choisir la bonne option : CASCADE, SET NULL ou RESTRICT

ON DELETE CASCADE n’est pas la seule option disponible. Le choix dépend de la relation sémantique entre parent et enfant.

Si la ligne enfant n’a de sens que par rapport au parent (ligne de commande sans commande, token sans utilisateur), CASCADE est le bon choix. Si la ligne enfant peut exister indépendamment mais perd sa référence (un article assigné à un auteur supprimé), SET NULL préserve la ligne en effaçant la référence. Si on veut bloquer la suppression du parent tant qu’il a des enfants, RESTRICT ou NO ACTION force la gestion côté application.

La cascade en chaîne – table A référence B qui référence C, toutes avec ON DELETE CASCADE – fonctionne mais amplifie les risques d’effacement non intentionnel. Sur ce type de schéma, une suppression mal ciblée peut effacer un volume de données difficile à anticiper sans avoir cartographié les dépendances au préalable.