How do I rename a primary key column in MySQL?

Question

score 26 · Accepted Answer · answered Apr 24 '10 at 05:09

26

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.

answered Apr 24 '10 at 05:09

Igor Serebryany

3,307
3
29
41

6

This works when there are no foreign keys. Another answer suggests dumping/restoring the whole database which will work but just dropping/recreating the foreign keys/indexes worked for me. – penfold Oct 03 '11 at 23:09
agree with @Pavel Rodionov, it throws the following error: [HY000][1025] Error on rename of xxx to yyy (errno: 150) – Remy Mellet Dec 05 '15 at 20:03

score 10 · Answer 2 · answered Jul 17 '15 at 03:13

10

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

answered Jul 17 '15 at 03:13

Dirigible

1,749
16
11

This answer does the trick when you are getting unexpected errors. Great! – Young Emil Dec 15 '17 at 13:04

score 6 · Answer 3 · answered May 19 '11 at 15:57

6

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.

answered May 19 '11 at 15:57

Alex

32,506
16
106
171

How I change this value? I do change for value 1 after? – Rafael Gomes Francisco Apr 13 '17 at 14:04
@RafaelGomesFrancisco what do you mean? – Alex Apr 17 '17 at 17:40

score 4 · Answer 4 · answered Feb 07 '12 at 16:15

4

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.

answered Feb 07 '12 at 16:15

Samuel DR

1,215
14
28

score 1 · Answer 5 · edited Sep 10 '20 at 09:29

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.

score 1 · Answer 6 · answered May 19 '11 at 15:53

1

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.

answered May 19 '11 at 15:53

Eric Clack

1,886
1
15
28

How do I rename a primary key column in MySQL?

6 Answers6

Linked