Adding an AUTO_INCREMENT to a Primary Key Column in MySql (After the Fact)
The other day I needed to add an
AUTO_INCREMENT to a primary key in a preexisting MySql database. After all, dropping and recreating the table would delete all my data.
So that means I just need to do an
ALTER TABLE command like the following, right:
ALTER TABLE my_table MODIFY COLUMN id INT AUTO_INCREMENT PRIMARY KEY;
Well, no. For some reason that command gave me the following error:
[Code: 1068, SQL State: 42000] Multiple primary key defined
Sigh. I’m not making a second primary key. It’s the only primary key. Besides, can’t I have more than one primary key anyway?
So, just to try things out I remove the
PRIMARY KEY from the end of my query:
ALTER TABLE my_table MODIFY COLUMN id INT AUTO_INCREMENT;
This time I get a different error:
[Code: 1833, SQL State: HY000] Cannot change column 'id': used in a foreign key constraint 'other_table_fk' of table 'other_table'
Different is good. It seems that I just need to not check foreign keys while I do the
ALTER (even though I’m not messing with any values!).
SET FOREIGN_KEY_CHECKS = 0; ALTER TABLE my_table MODIFY COLUMN id INT AUTO_INCREMENT; SET FOREIGN_KEY_CHECKS = 1;
So I do these three commands, double check my table definition, and everything looks good.