PostgreSQL中如何实现带外键约束的级联父级删除?

Linda Hamilton
发布: 2024-10-31 22:34:29
原创
972 人浏览过

How to Implement Cascading Parent Deletion with Foreign Key Constraints in PostgreSQL?

带外键约束的级联父级删除

在关系数据库系统中,删除子行通常需要删除父行(如果是)不再被任何其他孩子引用。此任务可以在 PostgreSQL 版本 9.1 及更高版本中使用数据修改 CTE(通用表表达式)来完成。

数据修改 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   -- !
   );</code>
登录后复制

此CTE 首先删除指定的子行。然后返回父 ID 和已删除的子 ID。如果父行没有其他剩余子行,第二个 DELETE 语句将使用此信息删除父行。条件c.child_id x.child_id 确保仅考虑除被删除的子级之外的子级。

消除竞争条件

防止并发事务可能导致意外结果的潜在竞争条件,可以在删除过程之前锁定父行。这是通过在 CTE 中使用 FOR NO KEY UPDATE 子句锁定父行来实现的。

<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>
登录后复制

这种方法将有风险的 DELETE 操作的执行限制在单个事务中,有效消除了并发的可能性干扰。

以上是PostgreSQL中如何实现带外键约束的级联父级删除?的详细内容。更多信息请关注PHP中文网其他相关文章!

来源:php.cn
本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
作者最新文章
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板