Comment implémenter la suppression en cascade d'un parent lors de la suppression d'un enfant dans PostgreSQL ?

Suppression du parent en cascade lors de la suppression de l'enfant

Dans les scénarios où une table parent comporte une colonne faisant référence aux lignes enfants, il devient nécessaire de supprimer le parent enregistrer s’il ne reste aucune ligne enfant. PostgreSQL propose différentes méthodes pour y parvenir.

Une approche consiste à utiliser un CTE (Common Table Expression) de modification des données dans PostgreSQL 9.1 ou version ultérieure :

<code class="sql">WITH del_child AS (
    DELETE FROM child
    WHERE  child_id = 1
    RETURNING parent_id, child_id
    )
DELETE FROM parent p
USING  del_child x
WHERE  p.parent_id = x.parent_id
AND    NOT EXISTS (
   SELECT FROM child c
   WHERE  c.parent_id = x.parent_id
   AND    c.child_id <> x.child_id   -- (1)
   );</code>

Grâce à ce CTE, l'enfant est toujours supprimé, tandis que le parent n'est supprimé que s'il n'a pas d'autres enfants. Notez que la condition (1) est cruciale pour éviter les résultats inattendus d’opérations simultanées.

Cependant, cette approche n’est pas à l’abri des conditions de concurrence. Pour les éliminer complètement, une ligne parent peut être verrouillée avant suppression :

<code class="sql">WITH lock_parent AS (
   SELECT p.parent_id, c.child_id
   FROM   child  c
   JOIN   parent p ON p.parent_id = c.parent_id
   WHERE  c.child_id = 12              -- (2)
   FOR    NO KEY UPDATE                -- (3)
   )
 , del_child AS (
   DELETE FROM child c
   USING  lock_parent l
   WHERE  c.child_id = l.child_id
   )
DELETE FROM parent p
USING  lock_parent l
WHERE  p.parent_id = l.parent_id
AND    NOT EXISTS (
   SELECT FROM child c
   WHERE  c.parent_id = l.parent_id
   AND    c.child_id <> l.child_id   -- (4)
   );</code>

Dans ce cas, une seule transaction peut verrouiller le même parent, empêchant ainsi les scénarios de suppression simultanée. (2) est la ligne enfant à supprimer. Tandis que (3) verrouille la ligne parent, (4) vérifie qu'aucun autre enfant n'existe avant de supprimer le parent.

Pour plus de clarté, les conditions (1) et (4) empêchent les « fausses » suppressions en garantissant que le parent est supprimé uniquement s'il n'a plus d'enfants restants.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!