How do I rename a primary key column in MySQL?

How do I rename a primary key column i开发者_开发百科n MySQL?

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.

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

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.

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.

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.

If others tables have a foreign key on your table, you cannot directly rename the column using alter table, it will throw 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 other tables

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