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

Question

I have 10 tables in my database, where tbl_city and tbl_state.

structure of tbl_state is below

state_id    |int(10)  |UNSIGNED ZEROFILL  auto_increment 
state_name  |varchar(40) 

and structure of tbl_city is below

city_id     |int(10)  |UNSIGNED ZEROFILL  auto_increment 
city_name   |varchar(40) |
state_code  |int(10)  | UNSIGNED ZEROFILL

where state_code is references to state_id of tbl_state, now my problem is

when i export all data and import again...then it gives foreign key constraint fails error.... bcoz

when we export mysql dump, sql dump is generated alphabetically ordered tables
and tbl_city comes before tbl_state in database.

please suggest me how do i manage this??

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