如何同时从父表和子表中删除数据

背景信息 #

现有两张PostgreSQL数据表，定义如下：

CREATE TABLE IF NOT EXISTS "public"."parent" (
  parent_id UUID not null,
  name TEXT not null,
)
CREATE TABLE IF NOT EXISTS "public"."child" (
  child_id UUID not null,
  parent_id UUID not null,
  name TEXT not null,
  CONSTRAINT "child_fk" FOREIGN KEY ("parent_id") REFERENCES "public"."parent"("parent_id")
)

尝试执行以下DELETE语句时触发语法错误：

DELETE FROM parent WHERE parent_id IN (DELETE FROM child WHERE name LIKE '%whatever' RETURNING parent_id)

已知单独执行DELETE FROM child WHERE name LIKE '%whatever' RETURNING parent_id是可行的，需要解释为何外层嵌套的写法不可行。

解答 #

能否执行？ #

不能，该写法不符合SQL语法规范，会直接触发语法错误。

不可行的原因 #

SQL标准以及PostgreSQL的语法规则中，IN子查询的内部必须是仅返回数据的SELECT查询，而DELETE属于数据修改语句（DML）——哪怕它带RETURNING子句能返回结果集，本质上还是执行数据变更的操作，不属于IN子查询允许的输入类型。

简单来说，IN的括号里只能放SELECT语句，不能放INSERT/UPDATE/DELETE这类会修改数据的语句，哪怕它们能返回结果也不行。

正确实现方式 #

如果要实现“删除指定child记录后，删除对应的parent记录”的需求，推荐使用CTE（WITH子句）来完成：

WITH deleted_child_parents AS (
  DELETE FROM child 
  WHERE name LIKE '%whatever' 
  RETURNING parent_id
)
DELETE FROM parent
WHERE parent_id IN (SELECT parent_id FROM deleted_child_parents);

这种写法通过CTE先执行child的删除操作并返回parent_id，再用这个结果集作为条件删除对应的parent记录，既符合语法规范，又能保证操作的原子性。

内容的提问来源于stack exchange，提问作者hatch