truncate foreign key constrained table

最新推荐文章于 2021-02-17 20:06:05 发布

原创最新推荐文章于 2021-02-17 20:06:05 发布 · 309 阅读

0 ·

CC 4.0 BY-SA版权

Mysql 专栏收录该内容

5 篇文章

订阅专栏

本文详细介绍了在MySQL数据库中执行TRUNCATE命令时遇到外键约束冲突的解决方案，包括如何使用设置FOREIGN_KEY_CHECKS为0，先清空相关表的数据，再进行TRUNCATE操作，最后恢复FOREIGN_KEY_CHECKS的默认值。通过实例演示，帮助开发者理解和解决此类常见问题。

执行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