在清空表中数据时报错:
[SQL]truncate table ACT_RE_DEPLOYMENT ;
[Err] 1701 - Cannot truncate a table referenced in a foreign key constraint (`act`.`ACT_GE_BYTEARRAY`, CONSTRAINT `ACT_FK_BYTEARR_DEPL` FOREIGN KEY (`DEPLOYMENT_ID_`) REFERENCES `act`.`ACT_RE_DEPLOYMENT` (`ID_`))
报错的意思大概是:不能清空具有外键约束的表*,然后有常识了delete语句,添加了伪条件where 1=1,记得之前有这样的情况然后还是报错,
原因:清空具有外键约束的表就会报错
[SQL]
DELETE FROM ACT_RE_DEPLOYMENT where 1=1;
[Err] 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`act`.`ACT_GE_BYTEARRAY`, CONSTRAINT `ACT_FK_BYTEARR_DEPL` FOREIGN KEY (`DEPLOYMENT_ID_`) REFERENCES `ACT_RE_DEPLOYMENT` (`ID_`))
解决方法是:
1.SET FOREIGN_KEY_CHECKS=0;
2.DELETE FROM ACT_RE_DEPLOYMENT where 1=1;
-- 或
-- truncate table ACT_RE_DEPLOYMENT ;
3.SET FOREIGN_KEY_CHECKS=1;
Mysql中如果表和表之间建立的外键约束,则无法删除表及修改表结构。
解决方法是:
1、首先取消外键约束:SET FOREIGN_KEY_CHECKS=0;
2、修改表数据或表结构
3、然后再设置外键约束: SET FOREIGN_KEY_CHECKS=1;