PostgreSQL：Table B外键为空时在Table A新建记录并更新外键

前提假设 #

假设两张表的基础结构如下（可根据实际业务调整字段）：

Table A（主表） #

CREATE TABLE table_a (
    id SERIAL PRIMARY KEY,
    -- 示例业务字段，可替换为实际字段
    display_name VARCHAR(50) DEFAULT '未命名'
);

Table B（从表，含指向Table A的外键） #

CREATE TABLE table_b (
    id SERIAL PRIMARY KEY,
    a_id INT REFERENCES table_a(id), -- 允许为空的外键字段
    -- 示例业务字段
    content TEXT
);

实现脚本 #

使用CTE（公共表表达式）完成「批量插入主表记录+更新从表外键」的原子操作：

WITH missing_fk_entries AS (
    -- 筛选Table B中外键为空的记录ID
    SELECT id AS b_id
    FROM table_b
    WHERE a_id IS NULL
), new_a_records AS (
    -- 为每条缺失外键的Table B记录，在Table A插入新记录并关联对应Table B的ID
    INSERT INTO table_a (display_name)
    -- 若需根据Table B字段填充Table A，可替换为对应的字段查询，比如：
    -- SELECT table_b.content FROM missing_fk_entries JOIN table_b ON missing_fk_entries.b_id = table_b.id
    SELECT '未命名'
    FROM missing_fk_entries
    RETURNING id AS new_a_id, missing_fk_entries.b_id
)
-- 将新生成的Table A主键更新到对应Table B的外键字段
UPDATE table_b
SET a_id = new_a_records.new_a_id
FROM new_a_records
WHERE table_b.id = new_a_records.b_id;

脚本说明 #

• 原子性保障：PostgreSQL中CTE与后续UPDATE属于同一事务，要么全部执行成功，要么全部回滚，避免数据不一致。
• 主键类型适配：若Table A主键为UUID，需先启用uuid-ossp扩展，插入时生成UUID：

  -- 仅需执行一次的扩展启用语句
  CREATE EXTENSION IF NOT EXISTS uuid-ossp;
  
  -- 修改后的插入语句
  INSERT INTO table_a (id, display_name)
  SELECT uuid_generate_v4(), '未命名'
  FROM missing_fk_entries
  RETURNING id AS new_a_id, missing_fk_entries.b_id
  

• 自定义填充逻辑：如果需要根据Table B的具体字段内容生成Table A记录，只需修改INSERT语句中的SELECT部分，关联Table B对应字段即可。

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