Mysql
無法刪除列:外鍵約束中需要
我有一個帶有兩個外鍵約束的表,如下所示:
mysql> SHOW CREATE TABLE `user`; CREATE TABLE `user` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `region_id` int(11) unsigned DEFAULT NULL, `town_id` int(11) unsigned DEFAULT NULL, `fullname` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, `username` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL, `email` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, `password` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, `active` tinyint(1) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `FK_G38T6P7EKUXYWH1` (`region_id`), KEY `FK_J8VWK0ZN7FD2QX4` (`town_id`), CONSTRAINT `FK_G38T6P7EKUXYWH1` FOREIGN KEY (`region_id`) REFERENCES `region` (`id`) ON UPDATE NO ACTION, CONSTRAINT `FK_J8VWK0ZN7FD2QX4` FOREIGN KEY (`town_id`) REFERENCES `town` (`id`) ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
儘管我禁用了外鍵列,但我無法刪除
FOREIGN_KEY_CHECKS
.mysql> ALTER TABLE `user` DROP COLUMN `region_id`; 1553 - Cannot drop index 'FK_G38T6P7EKUXYWH1': needed in a foreign key constraint mysql> SHOW VARIABLES LIKE 'FOREIGN_KEY%'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | foreign_key_checks | ON | +--------------------+-------+ 1 row in set mysql> SET FOREIGN_KEY_CHECKS = 0; Query OK, 0 rows affected mysql> SHOW VARIABLES LIKE 'FOREIGN_KEY%'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | foreign_key_checks | OFF | +--------------------+-------+ 1 row in set mysql> ALTER TABLE `user` DROP COLUMN `region_id`; 1828 - Cannot drop column 'region_id': needed in a foreign key constraint 'FK_G38T6P7EKUXYWH1'
查看MySql 文件,我發現了一個警告
foreign_key_keys
:警告
如果 foreign_key_checks=0,刪除外鍵約束所需的索引會使表處於不一致狀態,並導致表載入時發生的外鍵檢查失敗。為避免此問題,請在刪除索引之前刪除外鍵約束(錯誤 #70260)。
恕我直言,您應該在刪除列之前刪除外鍵。
ALTER TABLE `user` DROP FOREIGN KEY `FK_G38T6P7EKUXYWH1`; ALTER TABLE `user` DROP COLUMN `region_id`;
我已經設置了一個 rextester 範例,請在此處查看。