I have a table with around 30 million rows in MySQL 5.5 using the InnoDB storage engine. This table has a foreign key that links to another table with around 3 million rows.

I want to add a primary key to the larger table, but am unsure of how best to go about this. There's no existing column (or set of columns) I can use for this, so how should I go about this?

I have seen some references to an internal InnoDB record ID, but have not found if this is accessible.

Otherwise, perhaps I will need to dump and reload the data, adding the id in manually.


  • Is their a combination of keys in the table that is naturally unique? If so a multi-column primary key might make sense. Jun 13 '11 at 18:00
  • Unfortunately there was no such composite key possible. Otherwise I agree that would make sense. I've edited the post to clarify that this was not possible.
    – Wodin
    Jun 14 '11 at 6:27

this may be the wrong way to go about it, but..

couldn't you just alter the table structure,

add a primary key column to the table


write a quick script to go through the entire table

adding an autoincrementing value to the new column?

  • 2
    Thanks for the suggestion. I think I would have to add the column as a non-primary-key and then convert it to primary key afterwards, but I've actually just accidentally discovered while testing on a small table, that adding an AUTO_INCREMENT column (as primary key) automatically fills in the ids. Was assuming it would just give me an error, so I think that's the answer.
    – Wodin
    Feb 1 '11 at 13:50
  • if the database is high use, you might want to test this on a noncritical table to make sure it doesn't lock it up for a long time
    – Patrick
    Feb 1 '11 at 13:52
  • @Patrick, thanks for the note of caution. The database is not currently in use and I fully expect this to lock the table for a long time.
    – Wodin
    Feb 1 '11 at 14:10
  • 1
    I did ALTER TABLE blah ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY; and it took 2 hours and 18 minutes :) I did not try writing to the table during that time, but reads worked fine.
    – Wodin
    Feb 2 '11 at 7:37
  • 1
    @Wodin wow, that's a long time for an alter table command. glad it worked out for you.
    – Patrick
    Feb 2 '11 at 13:39

Here's how I would approach this:

  1. Create a new table with the desired structure including your PRIMARY KEY w/AUTO_INCREMENT and probably one other indexed field.
  2. Take a downtime to stop all traffic.
  3. Confirm you have a legit backup of your data.
  4. Copy all your rows from your existing table into the target table leveraging the AUTO INCREMENT to create your ids. *e.g. INSERT INTO target_table (col1,col2,etc) SELECT (col1,col2,etc) FROM origin_table;*
  5. Test that the data in the new table is accurate.
  6. RENAME the old table to origin_table_BAK.
  7. RENAME target_table to origin_table.
  8. Smoke test your application.
  9. Open back to traffic.
  10. After your next backup, drop origin_table_BAK.

*Note: If the origin table is InnoDB, foreign key constraints will be maintained and survive the renaming. So you'll have to do an alter to remedy that.

  • 3
    If you need to do it live, do steps 6 and 7 in a single RENAME, thereby making it atomic. RENAME TABLE real_name TO old, new TO real_name;
    – Rick James
    Apr 5 '12 at 19:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.