MySQL 无法删除外键

unable to drop the foreign key

提问by solomon

I would like to drop the foreign key in my table but been into this error message

我想删除表中的外键，但遇到此错误消息

mysql> alter table customers drop foreign key customerid;
ERROR 1025 (HY000): Error on rename of '.\products\customers' to '.\products\#sql2-7ec-a3' (errno: 152)
mysql>

采纳答案by Fahim Parkar

To avoid getting this error while trying to drop a foreign key, use the constraint name rather than the column name of the foreign key.

为避免在尝试删除外键时出现此错误，请使用约束名称而不是外键的列名称。

When I tried

当我尝试

mysql> ALTER TABLE mytable DROP PRIMARY KEY;

I got error as

我有错误

ERROR 1025 (HY000): Error on rename of '.\database\#sql-454_3' to '.\database\mytable' (errno: 150).

I solved it using:

我使用以下方法解决了它：

mysql> ALTER TABLE mytable DROP PRIMARY KEY, ADD PRIMARY KEY (column1,column2,column3);

Some links that will help you.

一些可以帮助您的链接。

link 1

链接 1

link 2[look for Posted by Alex Blume on November 7 2008 5:09pm & Posted by Hector Delgadillo on January 21 2011 4:57am]

链接 2[查找 Alex Blume 于 2008 年 11 月 7 日下午 5:09 发布和 Hector Delgadillo 于 2011 年 1 月 21 日凌晨 4:57 发布]

回答by bbrame

The solution described here by Chris Whiteworked for me.

Chris White在此处描述的解决方案对我有用。

The root problem is that MySQL creates both an index and a foreign key. Both must be removed (the foreign key first contrary to what Chris said).

根本问题是 MySQL 创建了索引和外键。两者都必须删除（外键首先与克里斯所说的相反）。

1. show create table table_name;

   SHOW CREATE TABLE `table_name`:
   
   | table_name | CREATE TABLE `table_name` (
     `id` int(20) unsigned NOT NULL auto_increment,
     `key_column` smallint(5) unsigned default '1',
     KEY `column_tablein_26440ee6` (`key_column`),  <--- shows key name
     CONSTRAINT `table_name_ibfk_1` FOREIGN KEY (`key_column`) REFERENCES <--- shows foreign key constraint name
   `second_table` (`id`) ON DELETE SET NULL
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
   

2. Delete the foreign key constraint:

   ALTER TABLE table_name DROP FOREIGN KEY `table_name_ibfk_1`;
   

3. Delete the key

   ALTER TABLE table_name DROP KEY `column_tablein_26440ee6`;
   

1. 显示创建表 table_name;

   SHOW CREATE TABLE `table_name`:
   
   | table_name | CREATE TABLE `table_name` (
     `id` int(20) unsigned NOT NULL auto_increment,
     `key_column` smallint(5) unsigned default '1',
     KEY `column_tablein_26440ee6` (`key_column`),  <--- shows key name
     CONSTRAINT `table_name_ibfk_1` FOREIGN KEY (`key_column`) REFERENCES <--- shows foreign key constraint name
   `second_table` (`id`) ON DELETE SET NULL
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
   

2. 删除外键约束：

   ALTER TABLE table_name DROP FOREIGN KEY `table_name_ibfk_1`;
   

3. 删除密钥

   ALTER TABLE table_name DROP KEY `column_tablein_26440ee6`;
   

That did it for me.

那是为我做的。

回答by Amarnasan

It looks like a bug in the error messaging of MySQL. (http://bugs.mysql.com/bug.php?id=10333)

它看起来像是 MySQL 错误消息中的一个错误。( http://bugs.mysql.com/bug.php?id=10333)

Use SHOW CREATE TABLE table_nameto see the actual nameof the foreign key. It looks like it might be mysql query browser problem when generating the query with wrong spelling of the foreign key name.

使用 SHOW CREATE TABLEtable_name查看外键的实际名称。在生成外键名称拼写错误的查询时，它看起来可能是 mysql 查询浏览器问题。

回答by Maksym Polshcha

To avoid getting this error while trying to drop a foreign key, use the constraint name rather than the column name of the foreign key

为避免在尝试删除外键时出现此错误，请使用约束名称而不是外键的列名称

回答by inf3rno

You should try with the foreign key name as Fahim Parkar suggested. Actually that does not work always either.

您应该尝试使用 Fahim Parkar 建议的外键名称。实际上，这也并不总是有效。

In my case I used the

在我的情况下，我使用了

FOREIGN KEY `fk`(`col1`) REFERENCES `table2`(`col1`)

code to add the fk by creation.

通过创建添加 fk 的代码。

The problem with this code that it is not valid and should throw some kind of syntax error, but still it added a foreign key with a random name.

这段代码的问题是它无效并且应该抛出某种语法错误，但它仍然添加了一个带有随机名称的外键。

When I added the fk with the right syntax:

当我使用正确的语法添加 fk 时：

CONSTRAINT `fk` FOREIGN KEY (`col1`) REFERENCES `table2`(`col1`)

the following code dropped it properly:

以下代码正确删除了它：

ALTER TABLE `table1` DROP FOREIGN KEY `fk`

So this kind of error can happen too if you try to remove a foreign key with an invalid name. It is important to view the table properties with

因此，如果您尝试删除名称无效的外键，也会发生这种错误。查看表属性很重要

SHOW CREATE TABLE `table1`

and check the foreign key names if you get this kind of errors.

如果遇到此类错误，请检查外键名称。