无法删除 MySQL 中的外键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25079645/
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
Can't Drop foreign key in MySQL
提问by Vato
I have had a 1 to many relationship between course and instructor, which I wanted to drop. When I tried to drop the instructorID in course table it told me that. I couldn't drop it as it was a foreign key. Then I decided to drop it like this:
我在课程和讲师之间建立了一对多的关系，我想放弃这种关系。当我试图在课程表中删除instructorID时，它告诉我。我无法删除它，因为它是一个外键。然后我决定像这样放弃它：
ALTER TABLE course DROP FOREIGN KEY instructorID
But i get this error :
但我收到此错误：
#1091 - Can't DROP 'InstructorID'; check that column/key exists
I don't get what this error means. what am i doing wrong?
我不明白这个错误是什么意思。我究竟做错了什么？
回答by kevdev
Please run an SHOW CREATE TABLE course;to make sure instructorID is the name of foreign key constraint.
请运行SHOW CREATE TABLE course;以确保instructorID 是外键约束的名称。
Additional: The error means MySQL searches for a foreign key constraint named "InstructorID" but there is no constraint with such name, maybe this is your column name, but you have to use the constraint name to delete foreign keys.
补充：错误表示MySQL搜索了一个名为“InstructorID”的外键约束，但是没有这个名字的约束，也许这是你的列名，但是你必须使用约束名来删除外键。
回答by Elham Kohestani
After you run SHOW CREATE table course;you should find the fk symbol which is commonly like the one bellow:
运行后，SHOW CREATE table course;您应该找到 fk 符号，它通常类似于下面的那个：
(course_ibfk_1)
it may differ according to your mysql version you are using then drop the foreign key using the fk symbol as follow :
它可能会根据您使用的 mysql 版本而有所不同，然后使用 fk 符号删除外键，如下所示：
alter table course drop foreign key course_ibfk_1;
回答by Dexter
If any of you still not able to DROPthe table. Try this. You can able to see all the details by running this
如果你们中的任何人仍然无法DROP上桌。尝试这个。您可以通过运行此查看所有详细信息
SELECT
TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'ReferenceTableName'; <-- change only this
If you want to see just the constrains
如果您只想查看约束
SELECT
CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'ReferenceTableName';
回答by Bobby
You need to delete the 'foreign key constraint' and the 'key'.
您需要删除“外键约束”和“键”。
Alter Table <table name> drop foreign key <constraint_name>
Alter table <table name> drop key <column name>
回答by PURVESH PATEL
To drop a FOREIGN KEY constraint:
要删除 FOREIGN KEY 约束：
MySQL:
MySQL：
ALTER TABLE Orders DROP FOREIGN KEY {Constraint/Key_name};
For SQL Server / Oracle / MS Access:
对于 SQL Server / Oracle / MS Access：
ALTER TABLE Orders DROP CONSTRAINT {Constraint/Key_name};
回答by jitendra
Can't DROP 'string'; check that column/key exists: ALTER TABLE accreditor_architecturesDROP stringerror show in terminal when remove column from data base in ruby on rails
不能删除“字符串”；检查列/键是否存在：当从 ruby on rails 中的数据库中删除列时，终端中显示ALTER TABLE accreditor_architecturesDROPstring错误
回答by JessicaRam
the reason why you cannot drop InstructorID is because you need to use the name of the constraint of the Foreign key . KevDev specified that you must run 'SHOW CREATE TABLE course' to find the constraint name. after doing so , you can delete the foreign key. BUT wait theres more, the 'key' still stays behind which must get deleted. You can run 'SHOW CREATE TABLE' course to check that the key is still behind. once checking that it is still there then perform what Bobby advised. 'Alter table drop key' in doing so you have fully removed the the foreign key
不能删除 InstructorID 的原因是因为需要使用外键的约束名称。KevDev 指定您必须运行“SHOW CREATE TABLE course”才能找到约束名称。这样做后，您可以删除外键。但是请稍等，“密钥”仍然保留在后面，必须将其删除。您可以运行“SHOW CREATE TABLE”课程来检查密钥是否还在后面。一旦检查它仍然存在，然后执行鲍比建议的操作。'Alter table drop key' 这样做你已经完全删除了外键
