如果没有任何其他子级引用，则删除父级

Question

我有一个示例情况：父表有一个名为id的列，在子表中作为外键引用。

删除子行时，如果父行未被任何其他子行引用，如何删除父行？

Answer 1

delete from child
where parent_id = 1

在子项中删除后，在父项中删除：

delete from parent
where
    id = 1
    and not exists (
        select 1 from child where parent_id = 1
    )

不存在条件将确保它仅在子级中不存在时才会被删除。您可以将两个删除命令包装在事务中：

begin;
first_delete;
second_delete;
commit;

Answer 2

在PostgreSQL 9.1或更高版本中，您可以使用使用数据修改CTE的单个语句来执行此操作。这通常不太容易出错。它最大限度地减少了两个DELETE之间的时间框架，其中竞争条件可能会导致并发操作的惊人结果：

sql prettyprint-override">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 1
   FROM   child c
   WHERE  c.parent_id = x.parent_id
   AND    c.child_id <> x.child_id   -- !
   );

数据库

在任何情况下都会删除该子项。我引用手册：

with中的数据修改语句只执行一次，并且始终执行到完成，与主查询是否读取其所有（或任何）输出无关。请注意，这与with中SELECT的规则不同：如上一节所述，SELECT的执行仅在主查询需要其输出的范围内进行。

注意最后一个条件。与预期相反，这是必要的，因为：

和中的子语句与主查询同时执行。因此，当使用＜code＞中的数据修改语句时，指定更新实际发生的顺序是不可预测的。所有语句都使用相同的快照执行（参见第13章），因此它们无法“看到”彼此对目标表的影响。

我用列名parent_id代替了非描述性的id。

为了完全消除上面提到的可能的争用条件，首先锁定父行。当然，所有类似的操作都必须遵循相同的程序才能工作。

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 1
   FROM   child c
   WHERE  c.parent_id = l.parent_id
   AND    c.child_id <> l.child_id   -- !
   );

这样，一次只有一个事务可以锁定同一个父事务。因此，多个事务删除同一父级的子项，仍然看到其他子项并保留父项，而所有子项之后都消失了，这是不可能的。（对于无密钥更新，仍允许对非密钥列进行更新。

如果这种情况从未发生过，或者您可以忍受它（几乎从未发生过）发生——第一个查询更便宜。否则，这是安全路径。

Postgres 9.4中引入了< code>FOR NO KEY UPDATE。手册中有详细说明。在旧版本中，请改用更强的锁< code>FOR UPDATE。