138 reputation
5
bio website
location
age
visits member for 3 years, 5 months
seen Jun 17 at 3:40

Jun
17
awarded  Supporter
Feb
4
awarded  Popular Question
Jun
14
awarded  Editor
Jun
14
revised What's a good way to add a PRIMARY KEY to a large InnoDB table?
Clarify that a composite key was also not possible.
Jun
14
comment What's a good way to add a PRIMARY KEY to a large InnoDB table?
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.
Feb
3
comment What's a good way to add a PRIMARY KEY to a large InnoDB table?
@Patrick, well it was quicker than I thought it might be :) Anyway, I'm also glad it worked. Thanks.
Feb
2
comment What's a good way to add a PRIMARY KEY to a large InnoDB table?
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.
Feb
1
awarded  Student
Feb
1
awarded  Scholar
Feb
1
accepted What's a good way to add a PRIMARY KEY to a large InnoDB table?
Feb
1
comment What's a good way to add a PRIMARY KEY to a large InnoDB table?
@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.
Feb
1
comment What's a good way to add a PRIMARY KEY to a large InnoDB table?
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.
Feb
1
asked What's a good way to add a PRIMARY KEY to a large InnoDB table?