Comment garantir la suppression du parent dans PostgreSQL lorsqu'aucun enfant n'y fait référence ?

Assurer la suppression du parent lorsqu'il n'est référencé par aucun enfant

Lors de la suppression d'une ligne enfant, il est souvent nécessaire de supprimer le parent comme bien s'il n'est plus référencé par aucun autre enfant. Ceci peut être réalisé dans PostgreSQL en utilisant un CTE de modification des données, minimisant ainsi le risque de conditions de concurrence.

Considérez l'instruction PostgreSQL suivante :

<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   -- !
   );</code>

Voici comment cela fonctionne :

• Le CTE del_child supprime la ligne enfant avec child_id = 1.
• L'instruction DELETE tente de supprimer la ligne parent en fonction du parent_id renvoyé par del_child. Cependant, il supprime le parent uniquement si aucun autre enfant ne le référence. Ceci est réalisé en vérifiant l'existence de tout autre enfant avec un child_id différent (sous-requête NOT EXISTS).
• La condition c.child_id <> x.child_id garantit que la sous-requête exclut la ligne enfant qui vient d'être supprimée.

Élimination des conditions de concurrence

Pour éliminer complètement la possibilité de conditions de concurrence , il est recommandé de verrouiller la ligne parent avant de supprimer à la fois l'enfant et le parent. Cela garantit qu'une seule transaction peut traiter cette opération à la fois, empêchant ainsi plusieurs transactions de supprimer des enfants et de laisser le parent orphelin.

Voici une instruction mise à jour avec verrouillage de ligne :

<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              -- provide child_id here once
   FOR    NO KEY UPDATE                -- locks parent row.
   )
 , 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   -- !
   );</code>

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!