Wie kann sichergestellt werden, dass übergeordnete Elemente in PostgreSQL gelöscht werden, wenn keine untergeordneten Elemente darauf verweisen?

Sicherstellen, dass das übergeordnete Element gelöscht wird, wenn kein untergeordnetes Element darauf verweist

Beim Löschen einer untergeordneten Zeile ist es häufig erforderlich, das übergeordnete Element als zu löschen Nun, wenn es von keinem anderen Kind mehr referenziert wird. Dies kann in PostgreSQL mithilfe eines datenmodifizierenden CTE erreicht werden, wodurch das Risiko von Race Conditions minimiert wird.

Beachten Sie die folgende PostgreSQL-Anweisung:

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

So funktioniert es:

• Der del_child CTE löscht die untergeordnete Zeile mit child_id = 1.
• Die DELETE-Anweisung versucht, die übergeordnete Zeile basierend auf der zurückgegebenen parent_id von del_child zu löschen. Das übergeordnete Element wird jedoch nur gelöscht, wenn keine anderen untergeordneten Elemente darauf verweisen. Dies wird erreicht, indem die Existenz eines anderen Kindes mit einer anderen child_id überprüft wird (NOT EXISTS-Unterabfrage).
• Die Bedingung c.child_id <> x.child_id stellt sicher, dass die Unterabfrage die soeben gelöschte untergeordnete Zeile ausschließt.

Eliminieren von Race Conditions

Um die Möglichkeit von Race Conditions vollständig auszuschließen wird empfohlen, die übergeordnete Zeile zu sperren, bevor sowohl die untergeordnete als auch die übergeordnete Zeile gelöscht werden. Dadurch wird sichergestellt, dass jeweils nur eine Transaktion diesen Vorgang verarbeiten kann, wodurch verhindert wird, dass mehrere Transaktionen untergeordnete Elemente löschen und das übergeordnete Element verwaist bleibt.

Hier ist eine aktualisierte Anweisung mit Zeilensperre:

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

Das obige ist der detaillierte Inhalt vonWie kann sichergestellt werden, dass übergeordnete Elemente in PostgreSQL gelöscht werden, wenn keine untergeordneten Elemente darauf verweisen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!