Comment implémenter la suppression parent en cascade avec des contraintes de clé étrangère dans PostgreSQL ?

Suppression de parent en cascade avec contrainte de clé étrangère

Dans les systèmes de bases de données relationnelles, la suppression d'une ligne enfant nécessite souvent la suppression du parent s'il est n’est plus référencé par aucun autre enfant. Cette tâche peut être accomplie dans PostgreSQL versions 9.1 et ultérieures à l'aide d'un CTE (Common Table Expression) de modification des données.

Approche CTE de modification des données

<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 CTE supprime d'abord la ligne enfant spécifiée. Il renvoie ensuite l'ID parent et l'ID enfant supprimé. La deuxième instruction DELETE utilise ces informations pour supprimer la ligne parent si elle n'a plus d'autres enfants. La condition c.child_id <> x.child_id garantit que seuls les enfants autres que celui supprimé sont pris en compte.

Élimination des conditions de concurrence

Pour éviter les conditions de concurrence potentielles où des transactions simultanées peuvent conduire à des résultats inattendus , la ligne parent peut être verrouillée avant le processus de suppression. Ceci est réalisé en utilisant la clause FOR NO KEY UPDATE dans le CTE pour verrouiller la ligne parent.

<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 approche limite l'exécution des opérations DELETE risquées à une seule transaction, éliminant ainsi la possibilité d'opérations simultanées. interférence.

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!