title: Database migration: auto-incremented foreign key trouble
body: <p>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: <code>threads</code> and <code>posts</code>. I was doing fine until I ran into some tricky auto-incremented foreign-key relationships.</p>
  
  <p><strong>Database schema</strong> (simplified to the important parts):</p>
  
  <pre><code>threads (id, title, user_id, created_at, updated_at)
  posts (id, thread_id, user_id, body, created_at, updated_at)
  </code></pre>
  
  <p>As you can see, <code>thread_id</code> is a foreign key corresponding to the <code>id</code> 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 <strong>auto-incremented</strong>. 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. </p>
  
  <p>How can I resolve this, inserting the threads/posts from the old data set into the new data set without screwing up the <code>id</code>s and relationships? Ways I have thought:</p>
  
  <ul>
  <li>add a large number to the old thread/post ids so they won't conflict with the new ones</li>
  <li>give up and walk away with my tail between my legs, figuring it's not possible</li>
  </ul>
  
  <p>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! <strong>The solution will be where the old data is added to the new database, and all posts still belong to the correct threads.</strong></p>