Mysql export sql dump alphabetically, which causes foreign key constraint error during import

Question

I have 10 tables in my database (MySQL). two of them are 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 - links to tbl_state.state_id .

now my problem

when I export all the tables and import again, it gives

foreign key constraint fails error.... because when I export mysql dump, the sql dump is generated in alphabetical order and tbl_city is delivered to tbl_state in the database.

Please suggest me how do I handle this script?

Is there a way that all tables go in the order of references to foreign keys?

Answer 1

You want to disable foreign key verification at the beginning of the dump, and then enable them after data reset:

 SET FOREIGN_KEY_CHECKS=0 ... dump ... SET FOREIGN_KEY_CHECKS=1 

Answer 2

If you are using SQLYog. Use this property.