今天学习使用sql语句删除数据时,会出现如下面内容的报错:
DELETE FROM orders WHERE order_num=20009
> 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`crashcourse`.`orderitems`, CONSTRAINT `fk_orderitems_orders` FOREIGN KEY (`order_num`) REFERENCES `orders` (`order_num`))
> 时间: 0.058s
截图
大概知道这是因为主键引起的原因,也查了下资料,有些说得比较复杂,也不好理解,然后发现了一个比较好理解又方便的办法:
1、关闭外键约束
2、执行删除语句
3、开启外键约束
代码如下:
-- 查看表结构
DESC orders;
-- 查询表数据
SELECT * FROM orders;
-- 需要删除数据前先关闭外键约束
SET FOREIGN_KEY_CHECKS=0;
-- 删除数据
DELETE FROM orders WHERE order_num=20009;
-- 删除数据后重新开启外键约束
SET FOREIGN_KEY_CHECKS=1;