如何在 PostgreSQL 中实现子级删除时级联父级删除？

子级删除时级联父级删除

在父表有引用子行的列的情况下，有必要删除父级记录是否没有剩余子行。 PostgreSQL 提供了多种方法来实现此目的。

一种方法是通过 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>

在这种情况下，只有一个事务可以锁定同一父行，从而防止并发删除的情况。 (2) 是要删除的子行。虽然 (3) 锁定父行，但 (4) 在删除父行之前验证不存在其他子行。

为了清楚起见，条件 (1) 和 (4) 通过确保父行是防止“错误”删除的仅当没有剩余子项时才删除。

以上是如何在 PostgreSQL 中实现子级删除时级联父级删除？的详细内容。更多信息请关注PHP中文网其他相关文章！