如何截断外键约束表?

2021-11-20 00:00:00 constraints foreign-keys mysql truncate dml

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

Why doesn't a TRUNCATE on mygroup work? Even though I have ON DELETE CASCADE SET I get:

ERROR 1701 (42000): 无法截断外键约束中引用的表 (mytest.instance, CONSTRAINT instance_ibfk_1 FOREIGN KEY (GroupID) 参考 mytest.mygroup (ID))

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 与删除).

You cannot TRUNCATE a table that has FK constraints applied on it (TRUNCATE is not the same as DELETE).

要解决此问题，请使用以下任一解决方案.两者都存在破坏数据完整性的风险.

To work around this, use either of these solutions. Both present risks of damaging the data integrity.

选项 1:

  1. 去除约束
  2. 执行TRUNCATE
  3. 手动删除现在引用无处
    4. 的行
  4. 创建约束

选项 2:由 user447951 在他们的回答中提出

SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE table $table_name; 
SET FOREIGN_KEY_CHECKS = 1;

相关文章