子が参照していないときに PostgreSQL で親レコードを削除する方法は?

親のカスケード削除: 子によって参照されなくなった親の削除

データベース システムでは、子テーブルが親テーブルを経由して参照する場合、外部キーを使用する場合、子行を削除するときに典型的なシナリオが発生します。他の子が参照していない場合、親行も削除する必要がありますか?

PostgreSQL 9.1 以降: データ変更 CTE アプローチ

PostgreSQL バージョン 9.1 以降の場合、データ変更 CTE を利用する 1 つのステートメントでこれを効率的に達成できます。

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

このステートメントは、指定された子の行を削除します。また、結果の親に行がない場合は、残りの子参照は親も削除します。 EXISTS サブクエリのキー条件により、すべての子が削除された場合にのみ親が削除されることが保証されます。

競合状態の排除

競合状態を完全に排除するには、次のようにします。削除が行われる前に親行をロックすることが重要です:

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

このメソッドにより、一度に 1 つのトランザクションだけが同じ親をロックできるようになり、複数のトランザクションがすべての子を削除して未解決の親が残るのを防ぎます。

以上が子が参照していないときに PostgreSQL で親レコードを削除する方法は?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。