mysql export sql dump alphabatically,which cause foreign key constraints error during import

Question

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?

Answer 1

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