13

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?

xkeshav
  • 53,360
  • 44
  • 177
  • 245

2 Answers2

13

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
reko_t
  • 55,302
  • 10
  • 87
  • 77
  • 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 – xkeshav Mar 24 '10 at 13:16
  • 1
    the technical person who is responsible for generating the dump should fix their script then. or use mysqldump which does this for you – reko_t Mar 24 '10 at 13:33
  • 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 – xkeshav Mar 24 '10 at 13:51
  • You can use mysqldump with the --xml flag: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html – Ike Walker Mar 24 '10 at 14:40
  • And here's an article on importing the XML data back into a MySQL database: http://dev.mysql.com/tech-resources/articles/xml-in-mysql5.1-6.0.html#xml-5.1-importing – Ike Walker Mar 24 '10 at 14:43
  • 1
    listen to reko_t and create a script for the "dumb" end user to execute. – Jon Black Mar 24 '10 at 15:17
0

By any chance if you use SQLYog. Use this property.enter image description here

A_01
  • 1,021
  • 11
  • 27