如何截断外键约束表？ [英] How to truncate a foreign key constrained table?

问题描述

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

错误1701（42000）：无法截断在外键约束中引用的表（ mytest 。 instance ，CONSTRAINT instance_ibfk_1 FOREIGN KEY（ GroupID ）参考 mytest 。 mygroup （ ID ））

  drop database mytest; 
创建数据库mytest; 
使用mytest; 
 
 CREATE TABLE mygroup（
 ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY 
）ENGINE = InnoDB; 
 
 CREATE TABLE实例（
 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：

1. 删除约束


2. 执行 TRUNCATE
现在引用无处

3. 创建约束


$ b 选项2： user447951 在答案中建议 em>

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




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

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;

解决方案

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.

Option 1:

1. Remove constraints
2. Perform TRUNCATE
3. Delete manually the rows that now have references to nowhere
4. Create constraints

Option 2: suggested by user447951 in their answer

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

这篇关于如何截断外键约束表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！