Foreign key constraints while dumping data

Question

mysqldump --compact --no-create-info -h192.168.150.180 -uroot -p live pnlbus_stops | sed s/pnlbus_stops/bus_stops/g | mysql test

I am getting an error: ERROR 1062 (23000) at line 1: Duplicate entry 'AN' for key 1

This is because bus_stops table in the test DB has foreign key constraints. How do I truncate the bus_stops table from test database in a SINGLE STATEMENT before inserting from "live" DB?

I have managed to solve it by adding echo "truncate table test.bus_stops" before mysqldump statement. But is there any better way? — shantanuo, Sep 05 '09 at 06:09
TRUNCATE TABLE? But I don't understand what you're trying to do... — derobert, Sep 05 '09 at 06:10

score 3 · Answer 1 · answered Sep 05 '09 at 06:11

3

put

set FOREIGN_KEY_CHECKS = 0;

at the top of your dump file

and put

SET FOREIGN_KEY_CHECKS = 1;

at the bottom of your dump file

answered Sep 05 '09 at 06:11

JeremyWeir

23,750
10
91
107

I just re-read your question, I don't understand what you're trying to do either – JeremyWeir Sep 05 '09 at 06:13

Foreign key constraints while dumping data

1 Answers1

Linked