Choisir une politique de suppression de clé étrangère PostgreSQL sans perdre les mauvaises lignes

Tracer un exemple isolé

Ce script illustratif utilise trois tables enfants différentes et trois lignes parentes différentes pour garder les politiques indépendantes. Il ne fixe pas trois politiques conflictuelles sur une même relation. Exécutez de telles démonstrations uniquement dans une session jetable ; la transaction se termine par ROLLBACK et ne prétend pas tester votre schéma de production.

Décider si l'enfant peut exister de lui-même

Une ligne de détail qui n'a pas de sens sans son parent peut convenir à une suppression en cascade. Un enregistrement historique qui doit rester peut nécessiter de restreindre la suppression ou de conserver la ligne avec une relation optionnelle. Commencez par cette décision métier au lieu de choisir l'action qui fait réussir un DELETE échouant.

Une clé étrangère protège la relation déclarée dans sa contrainte. Elle n'impose pas elle-même une politique de rétention, n'obtient pas l'autorisation de supprimer des données personnelles, ni ne préserve des fichiers externes associés à l'enregistrement.

Inspecter la contrainte qui s'applique réellement

Trouvez la table référencée, les colonnes référençant et l'action ON DELETE configurée dans le schéma de base de données ou l'outil administratif. Ne déduisez pas l'action du nom d'une colonne ou d'un modèle ORM qui peut ne pas correspondre à la base de données déployée.

CASCADE et SET NULL se placent après la clause REFERENCES dans la définition de la clé étrangère. Une commande telle que DELETE FROM parent WHERE id = 1 CASCADE n'est pas une syntaxe PostgreSQL DELETE pour choisir une action de clé étrangère.

Comparer les trois actions

Avec RESTRICT, une tentative de suppression d'un parent encore référencé par un enfant est rejetée. Avec CASCADE, la suppression du parent supprime les enfants référençants. Avec SET NULL, les enfants restent mais leurs colonnes référençantes deviennent nulles. D'autres contraintes s'appliquent toujours et peuvent empêcher l'opération.

SET NULL n'est approprié que lorsque les colonnes concernées et la logique applicative acceptent null. Une contrainte NOT NULL peut faire échouer la suppression. NO ACTION est une autre politique, mais elle ne doit pas être décrite comme identique à RESTRICT dans toutes les situations : la vérification différée des contraintes peut rendre la distinction importante.

BEGIN;
CREATE TEMP TABLE demo_parent (id integer PRIMARY KEY);
CREATE TEMP TABLE demo_restrict (
    id integer PRIMARY KEY,
    parent_id integer REFERENCES demo_parent(id) ON DELETE RESTRICT
);
CREATE TEMP TABLE demo_cascade (
    id integer PRIMARY KEY,
    parent_id integer REFERENCES demo_parent(id) ON DELETE CASCADE
);
CREATE TEMP TABLE demo_null (
    id integer PRIMARY KEY,
    parent_id integer REFERENCES demo_parent(id) ON DELETE SET NULL
);
INSERT INTO demo_parent VALUES (1), (2), (3);
INSERT INTO demo_restrict VALUES (10, 1);
INSERT INTO demo_cascade VALUES (20, 2);
INSERT INTO demo_null VALUES (30, 3);
DELETE FROM demo_parent WHERE id IN (2, 3);
SELECT count(*) AS remaining_cascade_rows FROM demo_cascade;
SELECT id, parent_id FROM demo_null ORDER BY id;
ROLLBACK;

Comprendre le résultat attendu et le cas rejeté

Le nombre de lignes en cascade attendu est zéro car l'enfant lié au parent 2 est supprimé. La ligne avec id 30 reste dans demo_null avec parent_id égal à null. Le script ne supprime pas le parent 1, donc son enfant restrict reste. Ces résultats découlent des contraintes indiquées et des données d'exemple ; ils sont illustratifs, pas des mesures.

Tenter de supprimer le parent 1 avant le rollback serait rejeté car demo_restrict le référence encore. Cette instruction délibérément échouante est omise du script : une erreur peut annuler la transaction, et le SQL suivant nécessite une gestion de rollback appropriée.

Vérifier la portée avant une suppression réelle

Comptez les lignes correspondant au prédicat parent proposé et inspectez les tables dépendantes. Les cascades peuvent continuer à travers d'autres relations et supprimer plus de lignes qu'un simple comptage d'une table enfant ne le suggère. Examinez la chaîne de dépendance complète et les conséquences applicatives.

Une requête de prévisualisation et un DELETE ultérieur ne constituent pas automatiquement une décision atomique protégée face aux changements concurrents. Planifiez la transaction requise et le comportement de verrouillage pour l'opération réelle plutôt que de supposer qu'un comptage antérieur fige les données.

Vérifier les performances sans affirmer une vitesse garantie

Supprimer une ligne référencée peut nécessiter de trouver des lignes du côté référençant. PostgreSQL ne crée pas automatiquement un index sur les colonnes de clé étrangère référençantes simplement parce que vous déclarez la clé étrangère. Vérifiez les index existants et la charge de travail avant de décider si un autre index est approprié.

Les grandes cascades peuvent maintenir des verrous, produire un travail substantiel de base de données et affecter les utilisateurs concurrents. Utilisez une procédure de maintenance appropriée et un plan de récupération. L'exemple de petites tables temporaires établit le comportement, pas le coût d'une suppression en production.

Garder les actions relationnelles séparées du nettoyage applicatif

Les contraintes de base de données agissent sur les relations déclarées dans la base de données. Elles ne garantissent pas le nettoyage de fichiers, de services distants ou d'événements maintenus en dehors de la transaction. Prévoyez explicitement ces effets secondaires.

Le rollback protège les changements transactionnels de la base de données dans l'exemple, pas des actions arbitraires effectuées par des systèmes externes. Si une politique déployée est incorrecte, considérez sa modification comme un changement de schéma examiné plutôt que comme un contournement rapide pour une seule requête échouante.