执行truncate的时候出现错误
[SQL]
TRUNCATE table person;
[Err] 1701 - Cannot truncate a table referenced in a foreign key constraint (`treasurewinner`.`game_spin_log`, CONSTRAINT `game_spin_log_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `treasurewinner`.`person` (`id`))
因为game_spin_log表有对person表的引用。所以失败了。
搜索答案如下:[url]http://stackoverflow.com/questions/5452760/truncate-foreign-key-constrained-table[/url]
SET FOREIGN_KEY_CHECKS=0;
TRUNCATE table1;
TRUNCATE table2;
SET FOREIGN_KEY_CHECKS=1;
这个FOREIGN_KEY_CHECKS是每次连接设置的,断开连接后会自动恢复成默认值
do we have to set SET FOREIGN_KEY_CHECKS=1; again afterwards? – makeramen Apr 9 at 21:29
I think you have to set it to 1 again, 'cause that is the default value. – PachinSV May 4 at 16:45
2
No, you don't. The setting is only valid during the connection. As soon as you disconnect, the next connection will have it set back to 1. – Pelle ten Cate Jun 27 at 8:07
[SQL]
TRUNCATE table person;
[Err] 1701 - Cannot truncate a table referenced in a foreign key constraint (`treasurewinner`.`game_spin_log`, CONSTRAINT `game_spin_log_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `treasurewinner`.`person` (`id`))
因为game_spin_log表有对person表的引用。所以失败了。
搜索答案如下:[url]http://stackoverflow.com/questions/5452760/truncate-foreign-key-constrained-table[/url]
SET FOREIGN_KEY_CHECKS=0;
TRUNCATE table1;
TRUNCATE table2;
SET FOREIGN_KEY_CHECKS=1;
这个FOREIGN_KEY_CHECKS是每次连接设置的,断开连接后会自动恢复成默认值
do we have to set SET FOREIGN_KEY_CHECKS=1; again afterwards? – makeramen Apr 9 at 21:29
I think you have to set it to 1 again, 'cause that is the default value. – PachinSV May 4 at 16:45
2
No, you don't. The setting is only valid during the connection. As soon as you disconnect, the next connection will have it set back to 1. – Pelle ten Cate Jun 27 at 8:07