#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.

Solution 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.

Solution 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`;

Solution 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.

Solution 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)

Solution 5

If you are trying to delete a column which is a FOREIGN KEY, you must find the correct name which is not the column name. Eg: If I am trying to delete the server field in the Alarms table which is a foreign key to the servers table.

1. SHOW CREATE TABLE alarm; Look for the CONSTRAINT `server_id_refs_id_34554433` FORIEGN KEY (`server_id`) REFERENCES `server` (`id`) line.
2. ALTER TABLE `alarm` DROP FOREIGN KEY `server_id_refs_id_34554433`;
3. ALTER TABLE `alarm` DROP `server_id`

This will delete the foreign key server from the Alarms table.

View more solutions

Comments

[Lekensteyn]

A quick search using Google gives me the idea that this problem is related to constraints. Possible helpful links: dev.mysql.com/doc/refman/5.0/en/innodb-error-codes.html and simplicidade.org/notes/archives/2008/03/mysql_errno_150.html

[MontrealDevOne]

I think this is the most accurate answer

[Ram Babu]

Your answer really helped me. DROP FOREIGN KEY solved the issue. Thanks

[Guilherme Ferreira]

In my case, the database was newly created, with no tables at all.

[Bancarel Valentin]

This solved my problem too, but the same script was working on my development MySQL. Is this a thing that changed in a version of MySQL ?