PostgreSQL で子の削除時に親のカスケード削除を実装するにはどうすればよいですか?

子の削除時の親の連鎖削除

親テーブルに子行を参照する列があるシナリオでは、親を削除する必要があります。子行が残っていない場合は記録します。 PostgreSQL は、これを実現するためのさまざまな方法を提供します。

1 つのアプローチは、PostgreSQL 9.1 以降のデータ変更 CTE (共通テーブル式) を使用することです。

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

この CTE を通じて、子は次のようになります。常に削除されますが、親は他に子がない場合にのみ削除されます。条件 (1) は、同時操作による予期せぬ結果を避けるために重要であることに注意してください。

ただし、このアプローチは競合状態の影響を受けないわけではありません。それらを完全に排除するには、削除前に親行をロックできます:

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

この場合、同じ親をロックできるのは 1 つのトランザクションのみであり、同時削除シナリオが防止されます。 (2) は削除する子行です。 (3) は親行をロックしますが、(4) は親を削除する前に他の子が存在しないことを確認します。

明確にするために、条件 (1) と (4) は親が確実に削除されることを保証することで「誤った」削除を防ぎます。子が残っていない場合にのみ削除されます。

以上がPostgreSQL で子の削除時に親のカスケード削除を実装するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。