在 MySQL 中删除带有外键的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21809749/
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
Drop Column with foreign key in MySQL
提问by Eduan Bekker
I have the following 2 tables:
我有以下 2 个表：
CREATE TABLE `personal_info` (
`p_id` int(11) NOT NULL AUTO_INCREMENT,
`name` text NOT NULL,
`initials` text NOT NULL,
`surname` text NOT NULL,
`home_lang` int(11) NOT NULL,
PRIMARY KEY (`p_id`),
KEY `home_lang` (`home_lang`),
CONSTRAINT `personal_info_ibfk_1` FOREIGN KEY (`home_lang`) REFERENCES `language_list` (`ll_id`)
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=latin1
CREATE TABLE `language_list` (
`ll_id` int(11) NOT NULL AUTO_INCREMENT,
`name` text NOT NULL,
PRIMARY KEY (`ll_id`)
) ENGINE=InnoDB AUTO_INCREMENT=73 DEFAULT CHARSET=latin1
I am trying to remove a column from a table with the following:
我正在尝试使用以下内容从表中删除一列：
ALTER TABLE `personal_info` DROP `home_lang`
But cannot do it since I recieve this error:
但不能这样做，因为我收到这个错误：
#1025 - Error on rename of '.\MyDB\#sql-112c_82' to '.\MyDB\personal_info' (errno: 150)
I have tried to first remove the index and then remove the column with this:
我试图先删除索引，然后删除列：
ALTER TABLE personal_info DROP INDEX home_lang
But then I get the following error:
但后来我收到以下错误：
#1553 - Cannot drop index 'home_lang': needed in a foreign key constraint
So I tried to drop the foreign key:
所以我试图删除外键：
ALTER TABLE personal_info DROP FOREIGN KEY home_lang
But received this error:
但收到此错误：
#1025 - Error on rename of '.\MyDB\personal_info' to '.\MyDB\#sql2-112c-8d' (errno: 152)
I have also tried to first set all the values to null:
我还尝试首先将所有值设置为 null：
update personal_info set home_lang = null
But then received this error:
但随后收到此错误：
#1452 - Cannot add or update a child row: a foreign key constraint fails (`MyDB`.`personal_info`, CONSTRAINT `personal_info_ibfk_1` FOREIGN KEY (`home_lang`) REFERENCES `language_list` (`ll_id`))
And now I am stuck. I have tried a few things but just cannot get the column removed. I am not allowed to alter the DB in any way other than removing the column.
现在我被困住了。我已经尝试了一些东西，但就是无法移除该列。除了删除列之外，我不允许以任何方式更改数据库。
回答by Marc B
Your DROP FOREIGN KEYsyntax is using the wrong key name. It's trying to drop your "plain" index on the home_langfield. It's NOT the foreign key itself.
您的DROP FOREIGN KEY语法使用了错误的键名。它试图将您的“普通”索引放在home_lang字段上。它不是外键本身。
CONSTRAINT `personal_info_ibfk_1` FOREIGN KEY (`home_lang`) REFERENCES `language_list` (`ll_id`)
^^^^^^^^^^^^^^^^^^^^^--- THIS is the name of the foreign key
Try:
尝试：
ALTER TABLE personal_info DROP FOREIGN KEY `personal_info_ibfk_1`
回答by Akshay Naik
Use this given below query to find the name of the foreign key.
使用下面给出的查询来查找外键的名称。
SHOW CREATE TABLE forms_main;
Then once u got the key, execute drop foreign key command
然后一旦你得到了密钥，就执行 drop外键命令
alter TABLE `forms_main`
drop FOREIGN key `forms_main_ibfk_1`;
Then execute the drop column command
然后执行drop column命令
ALTER TABLE `forms_main` DROP `company_id`;
回答by Hithaisri M K
ALTER TABLE db_name.table_nameDROP FOREIGN KEY foreign_key;
ALTER TABLE test.examDROP INDEX id;
更改表db_name。table_name删除外键foreign_key；更改表test。exam下降指数id；
