Adding an AUTO_INCREMENT to a Primary Key Column in MySql (After the Fact)



This site utilizes Google Analytics, Google AdSense, as well as participates in affiliate partnerships with various companies including Amazon. Please view the privacy policy for more details.

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.

Comments temporarily disabled.