如果没有任何其他子项引用，则删除父项 [英] Delete parent if it's not referenced by any other child

问题描述

我有一个示例情况:parent 表有一个名为 id 的列，在 child 表中作为外键引用.

I have an example situation: parent table has a column named id, referenced in child table as a foreign key.

删除子行时，如果父行没有被其他子行引用，如何删除父行?

When deleting a child row, how to delete the parent as well if it's not referenced by any other child?

推荐答案

在 PostgreSQL 9.1 或更高版本中，您可以使用 数据修改 CTE.这通常不太容易出错.它最小化两个 DELETE 之间的时间范围，其中 竞争条件可能导致并发操作的惊人结果:

In PostgreSQL 9.1 or later you can do this with a single statement using a data-modifying CTE. This is generally less error prone. It minimizes the time frame between the two DELETEs in which a race conditions could lead to surprising results with concurrent operations:

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   -- !
   );

SQL 小提琴.

无论如何都会删除孩子.我引用手册:

The child is deleted in any case. I quote the manual:

WITH 中的数据修改语句只执行一次，并且始终完成，与主查询是否读取无关他们的所有(或实际上任何)输出.请注意，这是不同的从 WITH 中 SELECT 的规则:如上一节所述，SELECT 的执行只进行到主查询要求它的输出.

Data-modifying statements in WITH are executed exactly once, and always to completion, independently of whether the primary query reads all (or indeed any) of their output. Notice that this is different from the rule for SELECT in WITH: as stated in the previous section, execution of a SELECT is carried only as far as the primary query demands its output.

只有在没有其他个子级时才会删除父级.
注意最后一个条件.与人们的预期相反，这是必要的，因为:

The parent is only deleted if it has no other children.
Note the last condition. Contrary to what one might expect, this is necessary, since:

WITH中的子语句并发相互执行并与主要查询.因此，当使用数据修改WITH 中的语句，指定的实际更新顺序发生是不可预测的.所有语句都以相同的方式执行快照(参见第 13 章)，因此他们无法看到"彼此的效果在目标表上.

The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot "see" each others' effects on the target tables.

我的大胆强调.
我使用列名 parent_id 代替了非描述性的 id.

Bold emphasis mine.
I used the column name parent_id in place of the non-descriptive id.

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

To eliminate possible race conditions I mentioned above completely, lock the parent row first. Of course, all similar operations must follow the same procedure to make it work.

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   -- !
   );

这种方式一次只有 一个 事务可以锁定同一个父级.所以不可能发生多个事务删除同一个父级的子级，仍然看到其他子级并保留父级，而所有子级都消失了.(仍然允许使用 FOR NO KEY UPDATE 更新非键列.)

This way only one transaction at a time can lock the same parent. So it cannot happen that multiple transactions delete children of the same parent, still see other children and spare the parent, while all of the children are gone afterwards. (Updates on non-key columns are still allowed with FOR NO KEY UPDATE.)

如果这种情况永远不会发生，或者您可以忍受(几乎不会)发生 - 第一次查询会更便宜.否则，这是安全路径.

If such cases never occur or you can live with it (hardly ever) happening - the first query is cheaper. Else, this is the secure path.

FOR NO KEY UPDATE 是在 Postgres 9.4 中引入的.手册中的详细信息. 在旧版本中使用用更强的锁FOR UPDATE代替.

FOR NO KEY UPDATE was introduced with Postgres 9.4. Details in the manual. In older versions use the stronger lock FOR UPDATE instead.

这篇关于如果没有任何其他子项引用，则删除父项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！