如果父级没有被任何子级引用，就删除父级。

Question

我有一个例子：在parent表中有一个名为id的列，作为外键被child表引用。

当删除一个子行时，如何删除其父级行，如果它没有被其他子行引用？

- zeroDivisible

Answer 1

在PostgreSQL 9.1或更高版本中，您可以使用一个语句来完成这个操作，使用数据修改的CTE。这通常更少出错。它最小化了两个DELETE之间的时间间隔，在这个时间间隔内，竞争条件可能会导致并发操作产生令人惊讶的结果。

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

fiddle
_{旧的sqlfiddle}

无论如何，子项都会被删除。我引用手册中的内容：

WITH中的数据修改语句只执行一次，并且始终完整执行，无论主查询是否读取它们的全部（或任何）输出。请注意，这与WITH中的SELECT的规则不同：如前一节所述，SELECT的执行仅限于主查询需要其输出的程度。

只有当父项没有其他子项时才会被删除。
请注意最后一个条件。与人们可能期望的相反，这是必要的，因为：

WITH中的子语句与主查询以及彼此并发执行。因此，在使用数据修改语句时，指定更新实际发生的顺序是不可预测的。所有语句都使用相同的快照（参见第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 FROM child c
   WHERE  c.parent_id = l.parent_id
   AND    c.child_id <> l.child_id   -- !
   );

这样一来，每次只能有一个事务锁定同一个父级。因此，不会出现多个事务删除同一个父级的子级，仍然可以看到其他子级并保留父级的情况，而所有子级在此之后都消失了。（仍然允许对非键列进行更新，使用FOR NO KEY UPDATE。）

如果从不发生这种情况，或者您可以接受它（几乎不会）发生-第一个查询更便宜。否则，这是安全的路径。 FOR NO KEY UPDATE是在Postgres 9.4中引入的。在旧版本中，请改用更强的锁FOR UPDATE。

- Erwin Brandstetter

---

是的，可能会发生。锁会在途中获取。一个并发事务可以在两个“DELETE”之间INSERT一个子项。更新：如果“INSERT”先提交，则父项上的“DELETE”将不会进行。如果“DELETE”先提交，我认为“INSERT”将最终出现外键违规并被回滚。虽然时间很短，但这种情况不太可能发生。 - Erwin Brandstetter

事务失败是一个令人惊讶的结果吗？ - Cameron Martin

根据使用情况和并发负载而定。我从未见过它失败，除非我为测试案例而故意挑衅它。 - Erwin Brandstetter

@KirillPetrov：您的建议编辑被拒绝了，但是您说得对：在许多设置中，必须完全排除竞态条件。我添加了一个改进的查询和一些解释。 - Erwin Brandstetter

@Felix：由于只有在没有子项时才会删除父项，因此在这种情况下，级联外键不会触发。但无论哪种方式都应该可以工作。如果仍然不清楚，请提出一个新问题。您始终可以引用此问题以获取上下文。 - Erwin Brandstetter

Answer 2

delete from child
where parent_id = 1

在子元素中删除后，需在父元素中执行此操作：

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

not exists条件将确保只有在子项中不存在时才会删除。您可以将两个删除命令都包装在一个事务中：

begin;
first_delete;
second_delete;
commit;

- Clodoaldo Neto