CODEWITHSUNDEEP
mysqlforeign-key-relationshipdump
xkeshav
xkeshav

Reputation: 54022

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

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?

Upvotes: 14

Views: 7139

Answers (2)

A_01
A_01

Reputation: 1141

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

Upvotes: 0

reko_t
reko_t

Reputation: 56430

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

Upvotes: 14

Related Questions