Comment supprimer les enregistrements parents dans PostgreSQL lorsqu'aucun enfant n'y fait référence ?

Suppression de parent en cascade : suppression d'un parent lorsqu'il n'est plus référencé par les enfants

Dans les systèmes de bases de données, lorsqu'une table enfant fait référence à une table parent via un clé étrangère, un scénario typique se produit lors de la suppression d'une ligne enfant : la ligne parent doit-elle également être supprimée si aucun autre enfant ne la référence ?

PostgreSQL 9.1 et versions ultérieures : approche CTE de modification des données

Pour PostgreSQL versions 9.1 ou ultérieures, une seule instruction utilisant un CTE de modification des données peut efficacement y parvenir :

<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>

Cette instruction supprime la ligne enfant spécifiée et, si le parent résultant n'a pas les références enfants restantes, supprime également le parent. La condition clé de la sous-requête EXISTS garantit que le parent n'est supprimé que si tous ses enfants ont été supprimés.

Élimination des conditions de concurrence

Pour éliminer complètement les conditions de concurrence, il est Il est crucial de verrouiller la ligne parent avant toute 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              -- 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>

Cette méthode garantit qu'une seule transaction peut verrouiller le même parent à la fois, empêchant ainsi plusieurs transactions de supprimer tous les enfants et de laisser un parent en suspens.

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!