带外键约束的表执行增删改操作违反约束报错问题求解

• 你定义的tb_register表存在外键约束fk_register_athlete，要求该表的athlete_id字段值必须提前在关联主表tb_athlete的athlete_id字段中存在，外键约束默认会在每条SQL执行时立即校验合法性。
• 你的事务执行顺序错误：先执行tb_register的插入操作，此时7777777这个运动员ID还没插入到tb_athlete表中，因此触发外键约束违规报错。
• 额外注意：tb_register还有另一组外键fk_register_round，要求插入的discipline_id+round_number组合必须提前存在于tb_round表中，若缺失也会触发同类报错。

方案1：调整SQL执行顺序（最推荐，性能无损耗，符合约束设计逻辑） #

外键关联的操作遵循「主表先写、子表后写；子表先删、主表后删」的规则即可，你的删除顺序本身是正确的，仅需要交换两条插入语句的顺序：

BEGIN;
-- 先插主表tb_athlete
INSERT INTO tb_athlete VALUES(7777777,'xxxxxx','XXX','xxxxxxx');
-- 再插子表tb_register
INSERT INTO tb_register VALUES(7777777,7,7,'2022-06-02 00:00:00',NULL,NULL);
-- 删除逻辑顺序正确，无需调整
DELETE FROM tb_register WHERE athlete_id = 1320573;
DELETE FROM tb_athlete WHERE athlete_id = 1320573;
COMMIT;

方案2：设置外键延迟校验（适用于必须调整顺序的特殊场景） #

如果业务逻辑要求必须先插子表再插主表，可以将外键约束设置为事务提交时再校验，而不是单条SQL执行时校验：

1. 先修改外键属性：

ALTER TABLE olympic.tb_register
ALTER CONSTRAINT fk_register_athlete DEFERRABLE INITIALLY DEFERRED;
-- 如有需要也可以同步修改fk_register_round的延迟属性

2. 调整后你的原SQL就可以正常执行，外键会等到事务COMMIT时统一校验合法性，只要事务结束前数据满足约束即可。

注意：该方案仅推荐特殊场景使用，常规业务优先使用方案1，避免误操作产生不符合约束的脏数据。

批量操作优化建议 #

如果需要执行大量插入/更新/删除操作，可使用CTE（公共表表达式）批量按顺序执行，比单条循环执行效率高3~10倍：

WITH insert_athlete AS (
    INSERT INTO tb_athlete (athlete_id, name, country, substitute_id)
    VALUES ('7777777','xxxxxx','XXX','xxxxxxx')
    RETURNING athlete_id
)
INSERT INTO tb_register (athlete_id, round_number, discipline_id, register_date, register_position, register_time, register_measure)
SELECT athlete_id,7,7,'2022-06-02'::DATE,NULL,NULL,NULL FROM insert_athlete;

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