实现子表插入空外键时自动创建父表记录的触发器及报错修正

原代码存在的核心问题 #

• 触发器时机完全错误：原代码将触发器设为AFTER INSERT执行，但子表的外键约束是在插入动作执行前就校验的。当你传入空的factor_id时，数据库在执行子表插入语句的第一阶段就会因找不到匹配的父表记录抛出1452错误，根本轮不到后续触发器逻辑运行。
• NULL判断语法错误：SQL中无法使用=NULL的方式判断字段是否为空，任何值和NULL做等值比较的结果都是UNKNOWN，判断分支永远不会触发，必须使用IS NULL语法完成空值判断。
• 表名拼写错误：父表实际名称为factors，原代码中写为factor，会触发表不存在的错误。
• 业务逻辑反向：原代码试图将还没生成的NEW.factor_id作为父表的主键插入，完全颠倒了流程——应该是先插入父表记录拿到自增主键，再将主键赋值给子表的外键字段。
• 表结构不满足需求：子表subfactors的factor_id字段被定义为NOT NULL，根本不允许传入空值，从根源上阻断了“外键为空时自动建父表”的触发条件。

具体修正步骤 #

1. 调整子表字段约束，允许factor_id传入空值：

   ALTER TABLE subfactors MODIFY COLUMN factor_id int(11) NULL;
   

2. 重写触发器逻辑，修正时机、语法、逻辑问题：

   DELIMITER //
   CREATE TRIGGER my_trigger BEFORE INSERT
   ON subfactors FOR EACH ROW
   BEGIN
       -- 识别外键为空的插入场景
       IF NEW.factor_id IS NULL THEN
           -- 向父表插入新记录，price_sum初始值取当前子表的price，时间取当前时间
           INSERT INTO factors(price_sum, `date`)
           VALUES (NEW.price, NOW());
           -- 获取父表刚生成的自增主键，赋值给当前子表的外键字段
           SET NEW.factor_id = 3549697;
       END IF;
   END //
   DELIMITER ;
   

修正后的运行逻辑 #

当你向子表插入记录且不传入factor_id时，触发器会在子表正式落库前提前运行：

• 先在父表factors中插入一条匹配规则的新记录
• 自动获取父表生成的自增主键ID，赋值给当前插入子表记录的factor_id字段
• 最后完成子表记录的插入，此时关联的外键值在父表中已存在，不会触发外键约束报错。

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