mysql导出sql转储alphabatically,导致导入过程中的外键约束错误 [英] mysql export sql dump alphabatically,which cause foreign key constraints error during import
问题描述
tbl_state
state_id | int(10)| UNSIGNED ZEROFILL auto_increment
state_name | varchar(40)
<
city_id | int(10)| UNSIGNED ZEROFILL auto_increment
city_name | varchar(40)|
state_code | int(10)| UNSIGNED ZEROFILL(FK reference with tbl_state.state_id)
外键约束: tbl_city.state_code 是对 tbl_state.state_id 的引用。
现在我的问题是
当我导出所有表并重新导入时,它给出
外键约束失败错误....
,因为当我导出mysql dump时,sql dump是按字母顺序排列的表
和 tbl_city 在数据库中的 tbl_state 之前生成的。
请问我该如何处理这种情况?
有没有办法让所有表格都进来外键引用的顺序?
您希望在转储启动时禁用外键检查,然后启用它们所有的数据都被转储:
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ ... $ ... $
SET FOREIGN_KEY_CHECKS = 1
I have 10 tables in my database(MySQL). two of them is given below
tbl_state
state_id |int(10) |UNSIGNED ZEROFILL auto_increment
state_name |varchar(40)
tbl_city
city_id |int(10) |UNSIGNED ZEROFILL auto_increment
city_name |varchar(40) |
state_code |int(10) | UNSIGNED ZEROFILL (FK reference with tbl_state.state_id)
Foreign Key Constraint : tbl_city.state_code is references to tbl_state.state_id .
now my problem is
when I export all tables and import again then it gives
foreign key constraint fails error....
because when I export mysql dump, sql dump is generated in alphabetically ordered tables
and tbl_city comes before tbl_state in database.
Please suggest me how do I handle this scenario?
Is there any way that all tables comes in the order of foreign key references?
You want to disable foreign key checks at start of the dump, and then enable them after all the data is dumped:
SET FOREIGN_KEY_CHECKS=0
... dump ...
SET FOREIGN_KEY_CHECKS=1
这篇关于mysql导出sql转储alphabatically,导致导入过程中的外键约束错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!