PostgreSQL中如何用单条DELETE语句删除出版物及关联子表记录

表结构与测试数据 #

创建表SQL #

CREATE table publications (
    id UUID PRIMARY KEY ,
    type CHAR(1) NOT NULL CHECK (type IN ('B', 'P')),
    title VARCHAR(60),
    publish_date DATE,
    author VARCHAR(50)
);

CREATE table books (
   id UUID PRIMARY KEY ,
   isbn VARCHAR(20),
   pages_no INT,
  CONSTRAINT books_fk FOREIGN KEY (id) REFERENCES publications(id)
);

CREATE TABLE posts (
   id UUID PRIMARY KEY ,
   comments_no INT,
   likes_no INT,
   unlikes_no INT,
   CONSTRAINT posts_fk FOREIGN KEY (id) REFERENCES publications(id)
);

测试数据插入SQL #

INSERT INTO publications (id, type, title, publish_date, author)
VALUES('5a428ea2-b5a9-48e0-8865-e2fe5f653473', 'B', 'Some book title', current_date, 'John Doe');

INSERT INTO publications (id, type, title, publish_date, author)
VALUES('15a7a75a-3ac7-4628-8735-174d8709bc4a', 'P', 'Some post title', current_date, 'John Doe');

INSERT INTO books (id, isbn, pages_no)
VALUES ('5a428ea2-b5a9-48e0-8865-e2fe5f653473', 'some isbn', 123);

INSERT INTO posts (id, comments_no, likes_no, unlikes_no)
VALUES ('15a7a75a-3ac7-4628-8735-174d8709bc4a', 10, 20, 3);

问题背景与核心需求 #

当前架构用于支持API返回某作者的所有出版物（书籍/帖子），同时需要实现根据ID删除出版物，无需提前知晓出版物类型，要求用单条语句同时删除publications父表记录及对应子表（books或posts）的关联记录。此前MySQL可用的多表DELETE方案无法在PostgreSQL中运行。

解决方案 #

1. 单条DELETE语句实现方式 #

PostgreSQL不支持MySQL式的多表DELETE语法，但可以借助CTE（公共表表达式）实现单语句删除：

WITH delete_sub AS (
    -- 根据publications的type匹配对应子表删除
    DELETE FROM books
    WHERE id = (SELECT id FROM publications WHERE id = '目标UUID' AND type = 'B')
    UNION ALL
    DELETE FROM posts
    WHERE id = (SELECT id FROM publications WHERE id = '目标UUID' AND type = 'P')
)
DELETE FROM publications WHERE id = '目标UUID';

该语句会先在CTE中处理对应类型的子表删除（只会命中匹配的子表），再删除父表记录，满足单语句且无需提前判断类型的需求。

2. 更优雅的长期方案：外键级联删除 #

上述CTE方案虽可行，但更简洁的方式是修改子表的外键约束，开启级联删除：

-- 删除原有外键约束（若已存在）
ALTER TABLE books DROP CONSTRAINT books_fk;
ALTER TABLE posts DROP CONSTRAINT posts_fk;

-- 重新添加带级联删除的外键
ALTER TABLE books ADD CONSTRAINT books_fk 
    FOREIGN KEY (id) REFERENCES publications(id) ON DELETE CASCADE;

ALTER TABLE posts ADD CONSTRAINT posts_fk 
    FOREIGN KEY (id) REFERENCES publications(id) ON DELETE CASCADE;

之后只需执行父表删除语句，PostgreSQL会自动级联删除对应子表的关联记录：

DELETE FROM publications WHERE id = '目标UUID';

这种方案无需额外逻辑，是最贴合需求的简洁实现。

3. 表继承方案的适用性说明 #

表继承确实能实现类似需求，但你的业务场景并不适配——继承更适合存在大量共同字段且需统一查询视图的场景，当前结构已通过publications表实现统一视图，引入继承会徒增复杂度，不建议采用。

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