创建含外键的表记录时触发外键约束失败错误排查

首先明确：你的操作顺序完全符合外键约束的逻辑（父表记录必须先存在，才能插入关联的子表记录），出现错误的原因是执行过程中某个环节存在疏漏，以下是排查和解决步骤：

1. 确认主表记录确实已成功插入 #

执行以下SQL查询，验证你插入的purchases记录存在，且id值和传入purchase_details的purchase_id完全一致：

SELECT id FROM purchases WHERE id = [你传入的purchase_id];

如果查询不到结果，说明主表记录未真正插入，可能的原因：

• 插入主表的SQL执行后未提交事务（若开启了事务），执行COMMIT;提交后再尝试插入子表。
• 插入主表时触发了其他约束（比如date或supplier字段为空），导致记录插入失败，检查主表插入操作的日志或错误信息。

2. 确保传入的purchase_id值准确 #

由于purchases的id是自增字段，插入主表时不要手动指定id值，让MySQL自动生成，然后直接获取该自增ID作为子表的purchase_id：

• 插入主表的SQL示例（不指定id）：

INSERT INTO purchases (date, supplier, total) VALUES ('2024-05-20', 1, 0.00);

• 插入后立即获取自增ID（以PHP为例）：

$purchase_id = mysqli_insert_id($conn);

用这个自动获取的ID插入子表，避免手动输入或传递错误的ID值。

3. 检查外键约束及关联字段的匹配性 #

• 确认purchase_details.purchase_id的字段类型（int(11) UNSIGNED）与purchases.id完全一致，且传入的purchase_id不是负数、0或超出范围的值。
• 同时检查products表是否存在，且你传入的product_id在products表中存在——purchase_details关联products的外键约束也可能触发相同的错误提示。

4. 临时关闭外键约束（仅用于测试排查） #

如果以上排查均无问题，可临时关闭外键约束验证：

SET FOREIGN_KEY_CHECKS = 0;
-- 插入purchase_details记录
INSERT INTO purchase_details (purchase_id, product_id, qty, price, total) VALUES ([你的purchase_id], 1, 2, 10.00, 20.00);
SET FOREIGN_KEY_CHECKS = 1;

若这样能成功插入，说明问题仍出在主表记录未正确存在；若仍报错，检查字段值是否符合其他约束（比如decimal精度、非空字段是否为空）。

补充：自动维护主表total字段 #

由于主表total是子表total的总和，建议用触发器自动维护，避免手动计算出错：

DELIMITER //
CREATE TRIGGER update_purchase_total AFTER INSERT ON purchase_details
FOR EACH ROW
BEGIN
    UPDATE purchases SET total = total + NEW.total WHERE id = NEW.purchase_id;
END //
DELIMITER ;

插入子表记录时，主表的total会自动累加，无需手动设置初始值。

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