#1025 - Error on rename of './database/#sql-2e0f_1254ba7' to './database/table' (errno: 150)

Question

So I am trying to add a primary key to one of the tables in my database. Right now it has a primary key like this:

PRIMARY KEY (user_id, round_number)

Where user_id is a foreign key.

I am trying to change it to this:

PRIMARY KEY (user_id, round_number, created_at)

I am doing this in phpmyadmin by clicking on the primary key icon in the table structure view.

This is the error I get:

#1025 - Error on rename of './database/#sql-2e0f_1254ba7' to './database/table' (errno: 150)

It is a MySQL database with InnoDB table engine.

Answer 1

There is probably another table with a foreign key referencing the primary key you are trying to change.

To find out which table caused the error you can run SHOW ENGINE INNODB STATUS and then look at the LATEST FOREIGN KEY ERROR section.

Answer 2

As was said you need to remove the FKs before. On Mysql do it like this:

ALTER TABLE `table_name` DROP FOREIGN KEY `id_name_fk`;

ALTER TABLE `table_name` DROP INDEX `id_name_fk`;

Answer 3

For those who are getting to this question via google... this error can also happen if you try to rename a field that is acting as a foreign key.

Answer 4

To bypass this in PHPMyAdmin or with MySQL, first remove the foreign key constraint before renaming the attribute.

(For PHPMyAdmin users: To remove FK constrains in PHPMyAdmin, select the attribute then click "relation view" next to "print view" in the toolbar below the table structure)