本文介绍了截断外键约束表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
为什么 TRUNCATE 在 mygroup
上不起作用?
即使我有 ON DELETE CASCADE SET
我得到:
Why doesn't a TRUNCATE on mygroup
work?Even though I have ON DELETE CASCADE SET
I get:
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
- 手动删除现在已引用无处的行
- 创建约束 $ b ,这是不好的做法。
Option 2: suggested by user447951 in his answer, which is bad practice. Use it if you are OK risking damage to data integrity.
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE table1;
SET FOREIGN_KEY_CHECKS = 1;
这篇关于截断外键约束表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!