结论:若一个table 中的某个column是其他table的foreign key,那么,该table不能使用truncate\drop。
两个table
TRUNCATE TABLE trans_parent;
DROP TABLE IF EXISTS trans_child_fk;
CREATE TABLE trans_child_fk
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
parent_id INT UNSIGNED NOT NULL,
created TIMESTAMP NOT NULL,
PRIMARY KEY(id),
INDEX(parent_id),
FOREIGN KEY(parent_id) REFERENCES trans_parent(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '设置了foreign key的table';
CREATE TABLE trans_parent
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
val VARCHAR(10) NOT NULL,
PRIMARY KEY(id),
UNIQUE KEY(val)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
向trans_parent插入数据后,执行
TRANCATE TABLE trans_parent
出现报错信息:
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`studysql`.`tran
s_child_fk`, CONSTRAINT `trans_child_fk_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `studysql`.`tra
ns_parent` (`id`))
再尝试
DROP TABLE trans_parent
报错信息:
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
要清空table trans_parent,方法是
You cannot TRUNCATE
a table that has FK constraints applied on it (truncate
is not the same as delete
).
To work around:
Option 1 which does not risk damage to data integrity:
- Remove constraints
- Perform
TRUNCATE
- Delete manually the rows that now have references to "nowhere"
- Create constraints
Option 2, which is bad practice, if you are OK risking damage to data integrity
SET FOREIGN_KEY_CHECKS=0; TRUNCATE table1; SET FOREIGN_KEY_CHECKS=1;
来自http://stackoverflow.com/questions/5452760/truncate-foreign-key-constrained-table
我先试一试Option 2
执行语句
SET FOREIGN_KEY_CHECKS=0;
TRUNCATE TABLE trans_parent;
SET FOREIGN_KEY_CHECKS=1;
通过了测试。
Option 1,另找时间测试。