MySQL 无法删除外键约束中所需的索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8482346/
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 Cannot drop index needed in a foreign key constraint
提问by user391986
I need to ALTER my existing database to add a column. Consequently I also want to update the UNIQUE field to encompass that new column. I'm trying to remove the current index but keep getting the error MySQL Cannot drop index needed in a foreign key constraint
我需要改变我现有的数据库来添加一列。因此，我还想更新 UNIQUE 字段以包含该新列。我正在尝试删除当前索引但不断收到错误MySQL Cannot drop index needed in a foreign key constraint
CREATE TABLE mytable_a (
ID TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
UNIQUE(Name)
) ENGINE=InnoDB;
CREATE TABLE mytable_b (
ID TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
UNIQUE(Name)
) ENGINE=InnoDB;
CREATE TABLE mytable_c (
ID TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
UNIQUE(Name)
) ENGINE=InnoDB;
CREATE TABLE `mytable` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`AID` tinyint(5) NOT NULL,
`BID` tinyint(5) NOT NULL,
`CID` tinyint(5) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `AID` (`AID`,`BID`,`CID`),
KEY `BID` (`BID`),
KEY `CID` (`CID`),
CONSTRAINT `mytable_ibfk_1` FOREIGN KEY (`AID`) REFERENCES `mytable_a` (`ID`) ON DELETE CASCADE,
CONSTRAINT `mytable_ibfk_2` FOREIGN KEY (`BID`) REFERENCES `mytable_b` (`ID`) ON DELETE CASCADE,
CONSTRAINT `mytable_ibfk_3` FOREIGN KEY (`CID`) REFERENCES `mytable_c` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB;
mysql> ALTER TABLE mytable DROP INDEX AID;
ERROR 1553 (HY000): Cannot drop index 'AID': needed in a foreign key constraint
回答by Brian Fisher
You have to drop the foreign key. Foreign keys in MySQL automatically create an index on the table (There was a SO Questionon the topic).
您必须删除外键。MySQL 中的外键会自动在表上创建索引（有一个关于该主题的SO 问题）。
ALTER TABLE mytable DROP FOREIGN KEY mytable_ibfk_1 ;
回答by Abhishek Goel
Step 1
第1步
List foreign key ( NOTE that its different from index name )
列出外键（注意它与索引名称不同）
SHOW CREATE TABLE <Table Name>
The result will show you the foreign key name.
结果将显示外键名称。
Format:
格式：
CONSTRAINT `FOREIGN_KEY_NAME` FOREIGN KEY (`FOREIGN_KEY_COLUMN`) REFERENCES `FOREIGN_KEY_TABLE` (`id`),
Step 2
第2步
Drop (Foreign/primary/key) Key
删除（外键/主键/键）键
ALTER TABLE <Table Name> DROP FOREIGN KEY <Foreign key name>
Step 3
第 3 步
Drop the index.
删除索引。
回答by ypercube??
If you mean that you can do this:
如果你的意思是你可以这样做：
CREATE TABLE mytable_d (
ID TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
UNIQUE(Name)
) ENGINE=InnoDB;
ALTER TABLE mytable
ADD COLUMN DID tinyint(5) NOT NULL,
ADD CONSTRAINT mytable_ibfk_4
FOREIGN KEY (DID)
REFERENCES mytable_d (ID) ON DELETE CASCADE;
> OK.
But then:
但是之后：
ALTER TABLE mytable
DROP KEY AID ;
gives error.
给出错误。
You can drop the index and create a new one in one ALTER TABLEstatement:
您可以删除索引并在一个ALTER TABLE语句中创建一个新索引：
ALTER TABLE mytable
DROP KEY AID ,
ADD UNIQUE KEY AID (AID, BID, CID, DID);
回答by Eli DM
Because you have to have an index on a foreign key field you can just create a simple index on the field 'AID'
因为你必须在外键字段上有一个索引，所以你可以在字段“AID”上创建一个简单的索引
CREATE INDEX aid_index ON mytable (AID);
and only then drop the unique index 'AID'
然后才删除唯一索引“AID”
ALTER TABLE mytable DROP INDEX AID;
回答by Stefan Mondelaers
A foreign key always requires an index. Without an index enforcing the constraint would require a full table scan on the referenced table for every inserted or updated key in the referencing table. And that would have an unacceptable performance impact. This has the following 2 consequences:
外键总是需要索引。如果没有强制执行约束的索引，则需要对引用表中的每个插入或更新的键在引用表上进行全表扫描。这会产生不可接受的性能影响。这有以下两个后果：
- When creating a foreign key, the database checks if an index exists. If not an index will be created. By default, it will have the same name as the constraint.
- When there is only one index that can be used for the foreign key, it can't be dropped. If you really wan't to drop it, you either have to drop the foreign key constraint or to create another index for it first.
- 创建外键时，数据库会检查索引是否存在。如果不是，将创建一个索引。默认情况下，它将与约束具有相同的名称。
- 当外键只能使用一个索引时，不能删除。如果您真的不想删除它，则必须先删除外键约束或为其创建另一个索引。
回答by Ram E Sh
I think this is easy way to drop the index.
我认为这是删除索引的简单方法。
set FOREIGN_KEY_CHECKS=1;
ALTER TABLE mytable DROP INDEX AID;
set FOREIGN_KEY_CHECKS=0;
回答by jav
In my case I dropped the foreign key and I still could not drop the index. That was because there was yet another table that had a foreign key to this table on the same fields. After I dropped the foreign key on the other table I could drop the indexes on this table.
就我而言，我删除了外键，但仍然无法删除索引。那是因为还有另一个表在相同字段上具有该表的外键。在我删除另一个表上的外键后，我可以删除该表上的索引。
回答by OMR
if you want to drop a Foreign key column (witch hold a constraint) you should drop the foreign key first then drop the column, when you drop the foreign key, you don't have to pass all the name, simply pass the Foreign key column name:
如果你想删除一个外键列（女巫持有一个约束），你应该先删除外键然后删除列，当你删除外键时，你不必传递所有的名称，只需传递外键列名：
$table->dropForeign(['currency_id']);
$table->dropColumn('currency_id');
more details on:
更多详情：
https://laravel.com/docs/6.x/migrations#foreign-key-constraints
https://laravel.com/docs/6.x/migrations#foreign-key-constraints
