How do I rename a primary key column in MySQL?

Question

How do I rename a primary key column in MySQL?

Answer 1

it's no different than altering any other column --

ALTER TABLE `pkey` CHANGE `keyfield` `keyfield2` INT( 11 ) NOT NULL AUTO_INCREMENT 

this changes the column keyfield in table pkey to be called keyfield2 -- you have to supply the definition afterwards, as usual.

Answer 2

If you are working with InnoDB then I think you cannot rename primary keys, at least you can't if they are referenced by foreign keys. You need to dump the database, rename the columns and referencing keys in the dump file, then reload the database.

Answer 3

Leave off the PRIMARY KEY part of the alter statement. The primary key will be updated automatically.

Answer 4

Maybe you have a foreign key constraint in place. You can disable those by SET foreign_key_constraints=0 but you have to remember to update the database afterwards.

Answer 5

Possible a bad practice work around. But you could export your entire db to an sql text file. Find and replace the PK you want to rename, and then restore the database over sql.

Answer 6

If others tables has foreign key on your table, you cannot directly rename the column using alter table, it will throws the following error: [HY000][1025] Error on rename of xxx to yyy (errno: 150) You must :

• drop foreign keys from others tables pointing to the primary key you want to rename
• rename the primary key
• add the foreign column to others tables

When renaming a table in Intellij, it generates you the code do drop and add the foreign key.