title: Changing MySQL primary key when foreign key contraints exist
body: <p>I have two already-existing tables which look (in part) roughly like this:</p>
  
  <pre><code>CREATE TABLE parent (
      old_pk CHAR(8) NOT NULL PRIMARY KEY
  ) ENGINE=InnoDB;
  
  CREATE TABLE child (
      parent_key CHAR(8),
      FOREIGN KEY (parent_key) REFERENCES parent(old_pk)
          ON UPDATE CASCADE ON DELETE CASCADE
  ) ENGINE=InnoDB;
  </code></pre>
  
  <p>I want to add a new auto-incrementing integer <code>id</code> column to <code>parent</code> and use it as the primary key instead, while still keeping <code>old_pk</code> as a unique key and allowing other tables like <code>child</code> to reference it in foreign key contraints.  Unfortunately, simply saying <code>ALTER TABLE parent DROP PRIMARY KEY</code> doesn't work:</p>
  
  <blockquote>
    <p>Error Code : 1025</p>
    
    <p>Error on rename of './data/#sql-4013_70f5e' to './data/parent' (errno: 150)</p>
  </blockquote>
  
  <p>Some googling suggests that this is due to the existing foreign key reference from <code>child</code>.  In essence, I need a way to tell MySQL "use this other column as the primary key, but don't forget the unique-key-ness of the original one".  Is there any way to accomplish this, other than just dropping the key constraints from <code>child</code> and reinstating them afterwards?  </p>
  
  <p>Assume that I must alter the tables in place, rather than creating copies with the same data and swapping them in later.  I've tried using <code>SET FOREIGN_KEY_CHECKS = 0</code> before altering the table, but it does not seem to help.</p>