ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint

结论：若一个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:

1. Remove constraints
2. Perform TRUNCATE
3. Delete manually the rows that now have references to "nowhere"
4. 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，另找时间测试。