mysql error 1025 (HY000): Error on rename of './foo' (errorno: 150) 是什么意思？

What does mysql error 1025 (HY000): Error on rename of './foo' (errorno: 150) mean?

提问by Trenton

I tried this in mysql:

我在mysql中试过这个：

mysql> alter table region drop column country_id;

And got this:

得到了这个：

ERROR 1025 (HY000): Error on rename of './product/#sql-14ae_81' to
'./product/region' (errno: 150)

Any ideas? Foreign key stuff?

有任何想法吗？外键的东西？

回答by Jeshurun

You usually get this error if your tables use the InnoDB engine. In that case you would have to drop the foreign key, and then do the alter table and drop the column.

如果您的表使用 InnoDB 引擎，您通常会收到此错误。在这种情况下，您必须删除外键，然后执行更改表并删除列。

But the tricky part is that you can't drop the foreign key using the column name, but instead you would have to find the name used to index it. To find that, issue the following select:

但棘手的部分是您不能使用列名删除外键，而是必须找到用于索引它的名称。要找到它，请发出以下选择：

SHOW CREATE TABLE region;

显示创建表区域；

This should show you the name of the index, something like this:

这应该会显示索引的名称，如下所示：

CONSTRAINT region_ibfk_1FOREIGN KEY (country_id) REFERENCES country(id) ON DELETE NO ACTION ON UPDATE NO ACTION

约束外region_ibfk_1键 ( country_id) 引用 country( id) ON DELETE NO Action ON UPDATE NO Action

Now simply issue an:

现在只需发出一个：

alter table region drop foreign key region_ibfk_1;

更改表区域删除外键 region_ibfk_1；

And finally an:

最后是：

alter table region drop column country_id;

更改表区域删除列 country_id；

And you are good to go!

你很高兴去！

回答by Harrison Fisk

It is indeed a foreign key error, you can find out using perror:

确实是外键错误，可以用perror查出来：

shell$ perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed

To find out more details about what failed, you can use SHOW ENGINE INNODB STATUSand look for the LATEST FOREIGN KEY ERROR section it contains details about what is wrong.

要了解有关失败的更多详细信息，您可以使用SHOW ENGINE INNODB STATUS并查找 LATEST FOREIGN KEY ERROR 部分，其中包含有关错误的详细信息。

In your case, it is most likely cause something is referencing the country_id column.

在您的情况下，很可能是因为某些内容引用了 country_id 列。

回答by Jeroen

You can get also get this error trying to drop a non-existing foreign key. So when dropping foreign keys, always make sure they actually exist.

尝试删除不存在的外键时，您也会收到此错误。因此，在删除外键时，请始终确保它们确实存在。

If the foreign key does exist, and you are still getting this error try the following:

如果外键确实存在，并且您仍然收到此错误，请尝试以下操作：

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

// Drop the foreign key here!

// 在这里删除外键！

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

This always does the trick for me :)

这对我来说总是有用的:)

回答by Muhammad Sohail

Simply run the alter table query using 'KEY' instead of 'FOREIGN KEY' in the drop statement. I hope it will help to solve the issue, and will drop the foreign key constraint and you can change the table columns and drop the table.

只需在 drop 语句中使用 'KEY' 而不是 'FOREIGN KEY' 运行更改表查询。我希望它有助于解决问题，并删除外键约束，您可以更改表列并删除表。

ALTER TABLE slide_image_sub DROP  KEY  FK_slide_image_sub;

here in DROP KEYinstead of DROP FOREIGN KEY,

在这里DROP KEY而不是DROP FOREIGN KEY，

hope it will help.

希望它会有所帮助。

Thanks

谢谢

回答by Baccata

I know, this is an old post, but it's the first hit on everyone's favorite search engine if you are looking for error 1025.

我知道，这是一篇旧帖子，但如果您正在寻找错误 1025，它是每个人最喜欢的搜索引擎上的第一篇文章。

However, there is an easy "hack" for fixing this issue:

但是，有一个简单的“hack”可以解决这个问题：

Before you execute your command(s) you first have to disable the foreign key constraints check using this command:

在执行命令之前，您首先必须使用以下命令禁用外键约束检查：

SET FOREIGN_KEY_CHECKS = 0;

Then you are able to execute your command(s).

然后你就可以执行你的命令了。

After you are done, don't forget to enable the foreign key constraints check again, using this command:

完成后，不要忘记再次启用外键约束检查，使用以下命令：

SET FOREIGN_KEY_CHECKS = 1;

Good luck with your endeavor.

祝你的努力好运。

回答by Joomler

I had a similar issues once. I deleted the primary key from TABLE A but when I was trying to delete the foreign key column from table B I was shown the above same error.

我曾经遇到过类似的问题。我从表 A 中删除了主键，但是当我尝试从表 BI 中删除外键列时，显示了上述相同的错误。

You can't drop the foreign key using the column name and to bypass this in PHPMyAdmin or with MySQL, first remove the foreign key constraint before renaming or deleting the attribute.

您不能使用列名删除外键，要在 PHPMyAdmin 或 MySQL 中绕过此功能，请先删除外键约束，然后再重命名或删除属性。

回答by Jan Tch?rm?n

Doing

正在做

SET FOREIGN_KEY_CHECKS=0;

before the Operation can also do the trick.

之前的操作也可以做到这一点。

回答by youngdero

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

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

Use SHOW CREATE TABLE categories to show the name of constraint.

使用 SHOW CREATE TABLE 类别显示约束的名称。

Most probably it will be categories_ibfk_1

很可能是 category_ibfk_1

Use the name to drop the foreign key first and the column then:

首先使用名称删除外键，然后删除列：

ALTER TABLE categories DROP FOREIGN KEY categories_ibfk_1;
ALTER TABLE categories DROP COLUMN assets_id;

回答by iltaf khalid

If you are using a client like MySQL Workbench, right click the desired table from where a foreign key is to be deleted, then select the foreign key tab and delete the indexes.

如果您使用的是 MySQL Workbench 之类的客户端，请右键单击要从中删除外键的所需表，然后选择外键选项卡并删除索引。

Then you can run the query like this:

然后你可以像这样运行查询：

alter table table_name drop foreign_key_col_name;

回答by marabol

Take a look in error file for your mysql database. According to Bug #26305my sql do not give you the cause. This bug exists since MySQL 4.1 ;-)

查看 mysql 数据库的错误文件。根据错误 #26305我的 sql 没有给你原因。此错误自 MySQL 4.1 起就存在；-)