Wie lösche ich übergeordnete Datensätze in PostgreSQL, wenn keine untergeordneten Datensätze darauf verweisen?

Kaskadierende übergeordnete Löschung: Löschen eines übergeordneten Elements, wenn es nicht mehr von untergeordneten Elementen referenziert wird

Wenn in Datenbanksystemen eine untergeordnete Tabelle über a auf eine übergeordnete Tabelle verweist Fremdschlüssel ergibt sich ein typisches Szenario beim Löschen einer untergeordneten Zeile: Sollte die übergeordnete Zeile auch gelöscht werden, wenn kein anderes untergeordnetes Element darauf verweist?

PostgreSQL 9.1 und höher: Datenmodifizierender CTE-Ansatz

Für PostgreSQL-Versionen 9.1 oder höher kann eine einzelne Anweisung, die einen datenmodifizierenden CTE verwendet, dies effizient erreichen:

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

Diese Anweisung löscht die angegebene untergeordnete Zeile und, wenn das resultierende übergeordnete Element keine hat verbleibende untergeordnete Referenzen, löscht auch das übergeordnete Element. Die Schlüsselbedingung in der EXISTS-Unterabfrage stellt sicher, dass das übergeordnete Element nur gelöscht wird, wenn alle seine untergeordneten Elemente gelöscht wurden.

Race Conditions beseitigen

Race Conditions vollständig eliminieren Es ist wichtig, die übergeordnete Zeile zu sperren, bevor eine Löschung erfolgt:

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

Diese Methode stellt sicher, dass jeweils nur eine Transaktion dasselbe übergeordnete Element sperren kann, wodurch verhindert wird, dass mehrere Transaktionen alle untergeordneten Elemente löschen und ein übergeordnetes Element frei bleibt.

Das obige ist der detaillierte Inhalt vonWie lösche ich übergeordnete Datensätze in PostgreSQL, wenn keine untergeordneten Datensätze darauf verweisen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!