Wie implementiert man die kaskadierende übergeordnete Löschung mit Fremdschlüsseleinschränkungen in PostgreSQL?

Kaskadierende übergeordnete Löschung mit Fremdschlüsseleinschränkung

In relationalen Datenbanksystemen erfordert das Löschen einer untergeordneten Zeile häufig die Löschung der übergeordneten Zeile, wenn dies der Fall ist von keinem anderen Kind mehr erwähnt. Diese Aufgabe kann in PostgreSQL-Versionen 9.1 und höher mithilfe eines datenmodifizierenden CTE (Common Table Expression) ausgeführt werden.

Datenmodifizierender CTE-Ansatz

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

Dies CTE löscht zunächst die angegebene untergeordnete Zeile. Anschließend werden die übergeordnete ID und die gelöschte untergeordnete ID zurückgegeben. Die zweite DELETE-Anweisung verwendet diese Informationen, um die übergeordnete Zeile zu löschen, wenn keine weiteren untergeordneten Zeilen mehr vorhanden sind. Die Bedingung c.child_id <> x.child_id stellt sicher, dass nur andere Kinder als das gelöschte berücksichtigt werden.

Beseitigung von Race Conditions

Um potenzielle Race Conditions zu verhindern, bei denen gleichzeitige Transaktionen zu unerwarteten Ergebnissen führen können , kann die übergeordnete Zeile vor dem Löschvorgang gesperrt werden. Dies wird erreicht, indem die FOR NO KEY UPDATE-Klausel im CTE verwendet wird, um die übergeordnete Zeile zu sperren.

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

Dieser Ansatz beschränkt die Ausführung der riskanten DELETE-Vorgänge auf eine einzige Transaktion und eliminiert so effektiv die Möglichkeit einer gleichzeitigen Ausführung Störung.

Das obige ist der detaillierte Inhalt vonWie implementiert man die kaskadierende übergeordnete Löschung mit Fremdschlüsseleinschränkungen in PostgreSQL?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!