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?

Spoonful

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

Germanophobe

i can't do this bcoz i m xporting data from a machine and importing on another machine, and this will be done by a non technical person. and this process will b continue once in a day forever... suggest me some other idea – Spoonful

the technical person who is responsible for generating the dump should fix their script then. or use mysqldump which does this for you – Germanophobe

can u tell me the link where i can find some script which export mysql into xml from one machine and import xml into mysql to another machine – Spoonful

You can use mysqldump with the --xml flag: dev.mysql.com/doc/refman/5.1/en/mysqldump.html – Topminnow

And here's an article on importing the XML data back into a MySQL database: dev.mysql.com/tech-resources/articles/… – Topminnow

listen to reko_t and create a script for the "dumb" end user to execute. – Laspisa

Answer 2

By any chance if you use SQLYog. Use this property.

Krafftebing