PostgreSQL关联表插入问题：OneToOne关系下关联ID使用失败

我有段时间没手动写SQL了，现在在PostgreSQL里给一对一关联的实体插入数据时遇到了问题。需要获取插入Courses表的ID，用来给Course_Content表插入数据。

实体关联关系：

• 一对一：course -> course_details
• 一对一：course_details -> course_content
• 一对多：course_content -> videos

我试了两种插入方式：
方式一：

INSERT INTO Courses(title, description, lessons, duration) VALUES ('A crash course','An awesome crash course','33','4 Hours') RETURNING id;
INSERT INTO Course_Content(id, section_title) values (id, 'A pretty awesome crash course');

方式二：

INSERT INTO Courses(title, description, lessons, duration) VALUES ('A crash course','An awesome crash course','33','4 Hours');
SELECT currval(pg_get_serial_sequence('Courses','id'));
INSERT INTO Course_Content(section_title) values ('A pretty awesome crash course');

两种方式都报了同样的错误：

[23503] ERROR: insert or update on table "course_content" violates foreign key constraint "coursedetail_fk" Detail: Key (id)=(1) is not present in table "course_detail".

问题出在第二个INSERT语句没法正确使用关联ID。以下是完整的建表SQL：

CREATE TABLE Courses(
    id SERIAL,
    title varchar(256),
    description varchar(256),
    lessons varchar(256),
    duration varchar(256),
    CONSTRAINT CoursesPK PRIMARY KEY (id)
);

CREATE TABLE Course_Detail(
    id SERIAL,
    title varchar(256),
    instructor varchar(256),
    introduction varchar(256),
    learn varchar(256),
    topics varchar(256),
    prerequisites varchar(256),
    goal varchar(256),
    additional_details varchar(256),
    highlevel_overview varchar(256),
    CONSTRAINT Course_fk FOREIGN KEY(id) REFERENCES Courses(id) on delete cascade on update cascade,
    CONSTRAINT CourseDetailPK PRIMARY KEY (id)
);

CREATE TABLE Course_Content(
    id SERIAL,
    section_title varchar(256),
    CONSTRAINT CourseDetail_fk FOREIGN KEY(id) REFERENCES Course_Detail(id) on delete cascade on update cascade,
    CONSTRAINT CourseContentPK PRIMARY KEY (id)
);

CREATE TABLE Videos(
    id SERIAL,
    title varchar(256),
    url varchar(256),
    paid bool,
    length varchar(256),
    CONSTRAINT CourseContent_fk FOREIGN KEY(id) REFERENCES Course_Content(id) on delete cascade on update cascade,
    CONSTRAINT VideosPK PRIMARY KEY (id)
);

错误根源 #

你报错的核心原因是跳过了Course_Detail表的插入步骤：Course_Content的外键id关联的是Course_Detail的主键，而非Courses的主键；同时你之前的方式没有正确捕获并传递上一步插入的ID值。

方法一：用WITH子句一次性完成关联插入 #

PostgreSQL的WITH（公共表表达式）可以串联多步插入，自动传递每一步的ID，适合这种关联场景：

-- 1. 插入Courses并返回ID
WITH inserted_course AS (
    INSERT INTO Courses(title, description, lessons, duration) 
    VALUES ('A crash course','An awesome crash course','33','4 Hours') 
    RETURNING id
),
-- 2. 插入Course_Detail，使用上一步的Course ID（一对一关联，ID保持一致）
inserted_detail AS (
    INSERT INTO Course_Detail(id, title, instructor) -- 按需填充其他字段
    SELECT id, 'Crash Course Detail', 'John Doe' FROM inserted_course
    RETURNING id
)
-- 3. 插入Course_Content，使用上一步的Course_Detail ID
INSERT INTO Course_Content(id, section_title)
SELECT id, 'A pretty awesome crash course' FROM inserted_detail;

方法二：用变量分步插入（适合客户端/命令行） #

如果是在psql命令行或支持变量的SQL客户端，可以把每一步的ID存到变量里，按顺序插入：

-- 插入Courses并将ID存入变量
INSERT INTO Courses(title, description, lessons, duration) 
VALUES ('A crash course','An awesome crash course','33','4 Hours') 
RETURNING id INTO :course_id;

-- 插入Course_Detail，复用course_id（一对一关联，ID一致）
INSERT INTO Course_Detail(id, title, instructor) 
VALUES (:course_id, 'Crash Course Detail', 'John Doe');

-- 插入Course_Content，同样复用course_id
INSERT INTO Course_Content(id, section_title) 
VALUES (:course_id, 'A pretty awesome crash course');

补充：插入关联Videos的示例 #

如果要继续插入一对多关联的Videos，同样遵循关联顺序即可：

WITH inserted_course AS (
    INSERT INTO Courses(title, description, lessons, duration) 
    VALUES ('A crash course','An awesome crash course','33','4 Hours') 
    RETURNING id
),
inserted_detail AS (
    INSERT INTO Course_Detail(id, title) 
    SELECT id, 'Course Detail Title' FROM inserted_course
    RETURNING id
),
inserted_content AS (
    INSERT INTO Course_Content(id, section_title)
    SELECT id, 'A pretty awesome crash course' FROM inserted_detail
    RETURNING id
)
-- 插入多个Videos，关联同一个Course_Content ID
INSERT INTO Videos(id, title, url, paid, length)
SELECT id, 'Video 1', 'http://example.com/video1', true, '10 Min' FROM inserted_content
UNION ALL
SELECT id, 'Video 2', 'http://example.com/video2', false, '15 Min' FROM inserted_content;

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