Database migration: auto-incremented foreign key trouble

Question

I'm trying to do a simple migration of some data from an old MySQL database to a new MySQL database. The data that I'm migrating is from a forum, so two tables: threads and posts. I was doing fine until I ran into some tricky auto-incremented foreign-key relationships.

Database schema (simplified to the important parts):

threads (id, title, user_id, created_at, updated_at)
posts (id, thread_id, user_id, body, created_at, updated_at)

As you can see, thread_id is a foreign key corresponding to the id of the thread the post belongs to. Here lies the problem: the new database already has threads and posts in it, and the primary keys are auto-incremented. It's not hard to see what the problem is: the thread and post ids in the two data sets won't match up/will conflict with each other! If I just filled in the posts table, they would now correspond to the wrong threads etc.

How can I resolve this, inserting the threads/posts from the old data set into the new data set without screwing up the ids and relationships? Ways I have thought:

• add a large number to the old thread/post ids so they won't conflict with the new ones
• give up and walk away with my tail between my legs, figuring it's not possible

Is there an easy way to do this that I'm missing? The "adding a large number" method doesn't seem ideal to me. Database wizards, please apply! The solution will be where the old data is added to the new database, and all posts still belong to the correct threads.

Answer 1

• find max thread id in new database MAX
• make a copy of the old database
• in your copy of the old database, UPDATE threads SET id=id+MAX and UPDATE posts SET thread_id=thread_id+MAX;
• import these remapped threads and posts into the new database - when you import the posts, use 0 for the id so that you do get a fresh post id though.