Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 6 down vote favorite

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.

Thanks.

share|improve this question
Is their a combination of keys in the table that is naturally unique? If so a multi-column primary key might make sense. – Justin Dearing 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

2 Answers

up vote 6 down vote accepted

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

then...

write a quick script to go through the entire table

adding an autoincrementing value to the new column?

share|improve this answer
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
show 2 more comments
up vote 1 down vote

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.

share|improve this answer
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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