使用别名插入多个SQL外键时报错，求正确实现方案

云上老周

2026-7-25

解决SQL插入外键时的CTE使用问题

第一个错误原因及修复

你用VALUES子句插入时，无法直接引用CTE（WITH定义的临时结果集）里的p.id，因为VALUES仅支持静态值或单行列变量。正确做法是改用INSERT ... SELECT语法，直接从CTE中选取数据：

WITH p AS (SELECT id FROM tbl_place WHERE txt_place = "Place 1")
INSERT INTO tbl_sheet(txt_rep_no, txt_song_name, id_place, id_source_by, id_compiled_by, id_notation_by, id_type, blob_sheet1)
SELECT "1", "Song Name 1", p.id, 1, 1, 1, "", ""
FROM p;

多个外键插入的正确写法

多个CTE不需要重复写WITH，只需在第一个WITH后用逗号分隔多个CTE定义。同时要确保多个CTE能正确组合（这里每个CTE仅返回单行，用CROSS JOIN关联即可）：

WITH 
p AS (SELECT id AS place_id FROM tbl_place WHERE txt_place = "Place 1"),
s AS (SELECT id AS source_id FROM tbl_person WHERE txt_person = "Person 1"),
c AS (SELECT id AS compiled_id FROM tbl_person WHERE txt_person = "Person 1"),
n AS (SELECT id AS notation_id FROM tbl_person WHERE txt_person = "Person 1")
INSERT INTO tbl_sheet(txt_rep_no, txt_song_name, id_place, id_source_by, id_compiled_by, id_notation_by, id_type, blob_sheet1)
SELECT "1", "Song 1", p.place_id, s.source_id, c.compiled_id, n.notation_id, 1, ""
FROM p
CROSS JOIN s
CROSS JOIN c
CROSS JOIN n;