无法删除列:外键约束中需要

Question

我有一个带有两个外键约束的表，如下所示：

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'

Answer 1

在查看MySql文档时，我发现了一个关于foreign_key_keys的警告：

使用警告foreign_key_checks=0，删除外键约束所需的索引会使表处于不一致的状态，并导致表加载时发生的外键检查失败。若要避免此问题，请在删除索引之前删除外键约束(Bug #70260)。

您应该在删除列之前删除外键。

ALTER TABLE `user` DROP FOREIGN KEY `FK_G38T6P7EKUXYWH1`;
ALTER TABLE `user` DROP COLUMN `region_id`;

我已经设置了一个rextester示例，请检查它这里。

Answer 2

在我的例子中，使用mysql 4.6.41，没有外键引用我的唯一索引，但是这个错误消息被抛出，并且我无法删除索引。

索引本身是复合索引，它使用3列，其中2列是其他列上的外键。一旦删除了这2列的外键，我就能够删除主复合索引。

hth