Wie implementiert man das kaskadierende Löschen von übergeordneten Elementen nach dem Entfernen von untergeordneten Elementen in PostgreSQL?

Kaskadierendes Löschen übergeordneter Elemente beim Entfernen untergeordneter Elemente

In Szenarios, in denen eine übergeordnete Tabelle eine Spalte enthält, die auf untergeordnete Zeilen verweist, ist es erforderlich, die übergeordnete Tabelle zu löschen aufzeichnen, wenn keine untergeordneten Zeilen mehr vorhanden sind. PostgreSQL bietet verschiedene Methoden, um dies zu erreichen.

Ein Ansatz ist ein datenmodifizierender CTE (Common Table Expression) in PostgreSQL 9.1 oder höher:

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

Durch diesen CTE ist das Kind immer gelöscht, während das übergeordnete Element nur gelöscht wird, wenn es keine anderen untergeordneten Elemente hat. Beachten Sie, dass Bedingung (1) von entscheidender Bedeutung ist, um unerwartete Ergebnisse aus gleichzeitigen Vorgängen zu vermeiden.

Dieser Ansatz ist jedoch nicht immun gegen Rennbedingungen. Um sie vollständig zu beseitigen, kann eine übergeordnete Zeile vor dem Löschen gesperrt werden:

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

In diesem Fall kann nur eine Transaktion dieselbe übergeordnete Zeile sperren, wodurch gleichzeitige Löschszenarien verhindert werden. (2) ist die zu löschende untergeordnete Zeile. Während (3) die übergeordnete Zeile sperrt, überprüft (4) vor dem Löschen des übergeordneten Elements, ob kein anderes untergeordnetes Element vorhanden ist.

Aus Gründen der Klarheit verhindern die Bedingungen (1) und (4) „falsche“ Löschungen, indem sie sicherstellen, dass das übergeordnete Element vorhanden ist Wird nur entfernt, wenn keine weiteren untergeordneten Elemente vorhanden sind.

Das obige ist der detaillierte Inhalt vonWie implementiert man das kaskadierende Löschen von übergeordneten Elementen nach dem Entfernen von untergeordneten Elementen in PostgreSQL?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!