I've got two MySQL databases: one with data, the other is empty. They have similar tables, and I want to copy data from db1.table to db2.table. The table contains parent_id FOREIGN KEY.
Here's my query:
INSERT INTO db2.table (id, name, parent_id) (SELECT id, name, parent_id FROM db1.table);
When I run it, it fails with foreign key constraint violation (parent_id).
But when I run this before query
ALTER TABLE db2.table DROP FOREIGN KEY
and this after query
ALTER TABLE db2.table ADD CONSTRAINT FOREIGN KEY
then all works fine. But I don't like this solution with 3 queries. I want to INSERT using one query.
So it looks like when INSERT runs, it processes rows in wrong order, so for some row parent_id isn't inserted yet, so violation fails.
How to do such INSERT?
