MySQL #1025 - 将“./database/#sql-2e0f_1254ba7”重命名为“./database/table”时出错（错误号：150）

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

提问by Richard Knop

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.

其中 user_id 是外键。

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.

我在 phpmyadmin 中通过单击表结构视图中的主键图标来执行此操作。

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.

它是一个带有 InnoDB 表引擎的 MySQL 数据库。

回答by Ike Walker

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 STATUSand then look at the LATEST FOREIGN KEY ERRORsection.

要找出导致错误的表，您可以运行SHOW ENGINE INNODB STATUS然后查看该LATEST FOREIGN KEY ERROR部分。

回答by Wellington Lorindo

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

如前所述，您之前需要删除 FK。在 Mysql 上这样做：

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

ALTER TABLE `table_name` DROP INDEX `id_name_fk`;

回答by Dave C

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.

对于那些通过谷歌解决这个问题的人......如果您尝试重命名充当外键的字段，也会发生此错误。

回答by LazerSharks

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

要在 PHPMyAdmin 或 MySQL 中绕过此问题，请在重命名属性之前先删除外键约束。

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

（对于 PHPMyAdmin 用户：要在 PHPMyAdmin 中删除 FK 约束，请选择属性然后单击表结构下方工具栏中“打印视图”旁边的“关系视图”）

回答by Bonnie Varghese

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.

如果您尝试删除属于 FOREIGN KEY 的列，则必须找到不是列名的正确名称。例如：如果我试图删除警报表中的服务器字段，它是服务器表的外键。

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`

1. SHOW CREATE TABLE alarm;寻找CONSTRAINT `server_id_refs_id_34554433` FORIEGN KEY (`server_id`) REFERENCES `server` (`id`)线路。
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.

这将从警报表中删除外键服务器。

回答by Mohammad Kermani

I had this problem, it is for foreign-key

我有这个问题，它是外键

Click on the Relation View(like the image below) then find name of the field you are going to remove it, and under the Foreign key constraint (INNODB)column, just put the select to nothing! Means no foreign-key

单击Relation View（如下图所示），然后找到要删除它的字段的名称，然后在该Foreign key constraint (INNODB)列下，将 select 置为空！意味着没有外键

Hope that works!

希望有效！

回答by StateLess

If you are adding a foreign key and faced this error, it could be the value in the child table is not present in the parent table.

如果您添加外键并遇到此错误，则可能是子表中的值不存在于父表中。

Let's say for the column to which the foreign key has to be added has all values set to 0 and the value is not available in the table you are referencing it.

假设必须添加外键的列的所有值都设置为 0，并且该值在您引用它的表中不可用。

You can set some value which is present in the parent table and then adding foreign key worked for me.

您可以设置一些存在于父表中的值，然后添加对我有用的外键。