無法刪除列：外鍵約束中需要

我有一個帶有兩個外鍵約束的表，如下所示：

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 範例，請在此處查看。

引用自：https://dba.stackexchange.com/questions/167564