PostgreSQL博客系统多对多关系的记录插入实现求助

你当前的表设计是一对多关系（一篇文章只能对应一个分类），但你需要的是多对多（一篇文章属于多个分类，一个分类包含多篇文章），所以必须调整表结构，新增一张中间关联表来实现。

1. 调整表结构 #

首先修改post表，移除原有的category_name字段（因为不需要直接关联分类）：

CREATE TABLE post (
   post_id INTEGER NOT NULL PRIMARY KEY,
   title VARCHAR(255),
   date DATE
);

然后创建中间关联表post_category，用来存储文章和分类的对应关系：

CREATE TABLE post_category (
   post_id INTEGER REFERENCES post(post_id) ON UPDATE CASCADE ON DELETE CASCADE,
   category_name VARCHAR(255) REFERENCES category(category_name) ON UPDATE CASCADE ON DELETE CASCADE,
   PRIMARY KEY (post_id, category_name) -- 联合主键避免重复关联同一文章和分类
);

2. 插入数据的正确方式 #

• 先往category表插入分类（支持批量插入）：

INSERT INTO category(category_name) VALUES ('technology'), ('website');

• 再往post表插入文章：

INSERT INTO post(post_id, title, date) VALUES (1, 'How to create a website', '2025-06-04');

• 最后往中间表插入文章与分类的关联记录，一篇文章对应多个分类就插多条：

INSERT INTO post_category(post_id, category_name)
VALUES (1, 'technology'), (1, 'website');

3. 查询示例 #

• 查询某篇文章的所有分类：

SELECT c.category_name
FROM post p
JOIN post_category pc ON p.post_id = pc.post_id
JOIN category c ON pc.category_name = c.category_name
WHERE p.post_id = 1;

• 查询某个分类下的所有文章：

SELECT p.post_id, p.title, p.date
FROM category c
JOIN post_category pc ON c.category_name = pc.category_name
JOIN post p ON pc.post_id = p.post_id
WHERE c.category_name = 'technology';

内容的提问来源于stack exchange，提问作者Hau Khua Lian