mysql 有外键关联时删除数据

2017年8月7日 · 阅读需 3 分钟

软件开发者与技术作者

MySQL 表之间存在外键关联时，直接删除父表记录可能出现 Cannot delete or update a parent row 错误。这是因为子表中仍有记录引用该数据，数据库通过外键约束阻止产生无效引用。

为什么无法删除数据

假设订单表 orders.user_id 引用了用户表 users.id。当某个用户仍有订单时执行：

DELETE FROM users WHERE id = 1001;

MySQL 会拒绝删除该用户，因为订单记录将失去对应的用户。

可以先查看表结构和外键定义：

SHOW CREATE TABLE orders;

最安全的方式是按照依赖关系操作，先删除或更新子表，再处理父表：

START TRANSACTION;

DELETE FROM orders WHERE user_id = 1001;
DELETE FROM users WHERE id = 1001;

COMMIT;

正式环境建议放在事务中执行，并在操作前确认影响行数：

SELECT COUNT(*) FROM orders WHERE user_id = 1001;

如果业务上允许父表数据删除时同步删除子表数据，可以在设计外键时配置 ON DELETE CASCADE：

ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE CASCADE;

级联操作会影响所有关联记录，使用前需要明确业务规则，避免误删历史数据。

在数据迁移、初始化或恢复备份时，可以针对当前会话临时关闭检查：

SET SESSION FOREIGN_KEY_CHECKS = 0;

-- 执行经过确认的数据导入、删除或更新操作

SET SESSION FOREIGN_KEY_CHECKS = 1;

这类做法不推荐用于日常生产操作。关闭检查并不会自动修复数据关系，错误操作可能留下孤儿数据。重新开启后，MySQL 也不会回头验证关闭期间已经写入的全部数据。

例如检查订单表中是否存在找不到用户的记录：

SELECT o.id, o.user_id
FROM orders o
LEFT JOIN users u ON u.id = o.user_id
WHERE u.id IS NULL;

如果查询返回数据，应先修复这些无效引用，再恢复正常业务操作。