Cannot drop column : needed in a foreign key constraint

Question

I have a table with two foreign key constraints as below:

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

I can't drop a foreign key column although I disable 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'

score 24 · Accepted Answer · answered Mar 19 '17 at 18:14

Having a look at MySql docs I've found a warning about foreign_key_keys:

Warning
With foreign_key_checks=0, dropping an index required by a foreign key constraint places the table in an inconsistent state and causes the foreign key check that occurs at table load to fail. To avoid this problem, remove the foreign key constraint before dropping the index (Bug #70260).

IMHO you should drop FOREIGN KEY before DROP the COLUMN.

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

I've set up a rextester example, check it here.

score 0 · Answer 2 · answered Sep 09 '20 at 10:13

In my case, using mysql 4.6.41, there was no foreign key referring to my unique index, however this error message were thrown and I was not able to delete the index.
The index itself was composite one, it used 3 columns; 2 of them were foreign keys on other columns. Once I deleted the foreign keys of these 2 columns I was able to delete the main composite index.
hth

Cannot drop column : needed in a foreign key constraint

2 Answers2