我有以下两张表:
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
我正在尝试使用以下代码从表格中删除一列:
ALTER TABLE `personal_info` DROP `home_lang`
但是我无法这样做,因为我收到了以下错误:
#1025 - Error on rename of '.\MyDB\#sql-112c_82' to '.\MyDB\personal_info' (errno: 150)
我已经尝试过先删除索引,然后使用以下代码删除列:
ALTER TABLE personal_info DROP INDEX home_lang
但是我遇到了以下错误:
#1553 - Cannot drop index 'home_lang': needed in a foreign key constraint
所以我尝试删除外键:
ALTER TABLE personal_info DROP FOREIGN KEY home_lang
但是收到了这个错误:
#1025 - Error on rename of '.\MyDB\personal_info' to '.\MyDB\#sql2-112c-8d' (errno: 152)
我也尝试过先将所有值设为 null:
update personal_info set home_lang = null
但是随后收到了这个错误:
#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`))
现在我卡住了。我尝试了一些方法,但就是无法删除该列。除了删除该列,我不允许以任何其他方式更改数据库。
SET FOREIGN_KEY_CHECKS = 0;
命令来禁用约束。 - Grijesh Chauhan