MySQL MySQL删除外键错误152
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11649092/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Drop foreign key Error 152
提问by xylar
I am trying to drop a number of foreign keys using:
我正在尝试使用以下方法删除一些外键：
ALTER TABLE `table` DROP FOREIGN KEY `fk_table_users1` , DROP FOREIGN KEY `fk_table_accounts1` , DROP FOREIGN KEY `fk_table_data1` ;
but it returns the error:
但它返回错误：
Error on rename of './db/table' to './db/#sql2-179c-288289' (errno: 152)
I have run SHOW ENGINE INNODB STATUSwhich says:
我跑了SHOW ENGINE INNODB STATUS它说：
120725 12:38:37 Error in dropping of a foreign key constraint of table db/table,
in SQL command
ALTER TABLE `table` DROP FOREIGN KEY `fk_table_users1` , DROP FOREIGN KEY `fk_table_accounts1` , DROP FOREIGN KEY `fk_table_data1`
Cannot find a constraint with the given id fk_table_users1.
SHOW CREATE TABLE 'table'output:
SHOW CREATE TABLE 'table'输出：
CREATE TABLE `table` (
`id` int(11) NOT NULL auto_increment,
`data_id` int(11) NOT NULL,
`account_id` int(11) NOT NULL,
`status` enum('pending','complete') NOT NULL default 'pending',
`created_at` datetime NOT NULL,
`created_by` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_orders_users1` (`created_by`),
KEY `fk_orders_data1` (`data_id`),
KEY `fk_orders_accounts1` (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
However when I look at the structure via phpmyadmin it lists the foreign key with the same name. Do I need to do something else before I can drop the foreign keys?
但是，当我通过 phpmyadmin 查看结构时，它列出了具有相同名称的外键。在删除外键之前，我需要做其他事情吗？
采纳答案by jsist
There are no foreign keys. Refer MySQL documentationwhich says
没有外键。请参阅MySQL 文档，其中说
KEY is normally a synonym for INDEX.
So basically in table you have created indexes, not foreign keys. For Foreign Key info, Click here
所以基本上在表中你已经创建了索引，而不是外键。有关外键信息，请单击此处
回答by Brett
You need to temporarily drop the constraint so that you can remove it.
您需要暂时删除约束，以便您可以删除它。
SET FOREIGN_KEY_CHECKS=0;
SET FOREIGN_KEY_CHECKS=0;
and then turn them on again after you drop the foreign key:
然后在删除外键后再次打开它们：
SET FOREIGN_KEY_CHECKS=1;
SET FOREIGN_KEY_CHECKS=1;
回答by user3065279
first drop foreign key then delete column
首先删除外键然后删除列
alter table 'table name' drop foreign key 'constraint id ;
更改表 '表名' 删除外键 '约束 id ;
if you don't know constraint id create database dump in that constraint id is available in dump file ..
如果您不知道约束 ID，请在该约束 ID 中创建数据库转储，在转储文件中可用..
then delete column..
然后删除列..
回答by Nuryagdy Mustapayev
The index name and constraint name may not be same. You should delete constraint first using code: ALTER TABLE tablename DROP FOREIGN KEY constraintname
索引名称和约束名称可能不同。您应该首先使用代码删除约束：ALTER TABLE tablename DROP FOREIGN KEY constraintname
