为什么mygroup上的TRUNCATE不起作用?
即使我有ON DELETE CASCADE SET我得到:
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (
mytest
.instance
, CONSTRAINTinstance_ibfk_1
FOREIGN KEY (GroupID
) REFERENCESmytest
.mygroup
(ID
))
drop database mytest;
create database mytest;
use mytest;
CREATE TABLE mygroup (
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=InnoDB;
CREATE TABLE instance (
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
GroupID INT NOT NULL,
DateTime DATETIME DEFAULT NULL,
FOREIGN KEY (GroupID) REFERENCES mygroup(ID) ON DELETE CASCADE,
UNIQUE(GroupID)
) ENGINE=InnoDB;
解决方法:
您不能TRUNCATE对其应用了FK约束的表(TRUNCATE与DELETE不同).
要解决此问题,请使用以下任一解决方案.两者都存在损害数据完整性的风险.
选项1:
>删除约束
>执行TRUNCATE
>手动删除现在引用无处的行
>创建约束
选项2:用户447951在their answer中建议
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE table $table_name;
SET FOREIGN_KEY_CHECKS = 1;