mysql – 如何截断外键约束表?

为什么mygroup上的TRUNCATE不起作用?
即使我有ON DELETE CASCADE SET我得到:

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (mytest.instance, CONSTRAINT instance_ibfk_1 FOREIGN KEY (GroupID) REFERENCES mytest.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;
上一篇:事务的特点


下一篇:DML、DDL、DCL是什么?