删除外键约束后无法删除自动创建的索引问题

环境与操作背景 #

使用MariaDB镜像 mariadb:10.5.8，删除名为fk_customers_store_user的外键约束后，执行SHOW CREATE TABLE customers得到如下表结构：

CREATE TABLE `customers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) DEFAULT NULL,
  `name` varchar(191) NOT NULL,
  `mobile` bigint(20) unsigned NOT NULL,
  `email` longtext NOT NULL,
  `image_url` longtext DEFAULT NULL,
  `owner_id` bigint(20) unsigned NOT NULL,
  `remarks` longtext DEFAULT NULL,
  `address` longtext DEFAULT NULL,
  `city` longtext DEFAULT NULL,
  `pincode` bigint(20) unsigned DEFAULT NULL,
  `state` longtext DEFAULT NULL,
  `cibil_score` bigint(20) unsigned DEFAULT NULL,
  `occupation` longtext DEFAULT NULL,
  `is_buyer` tinyint(1) DEFAULT 0,
  `is_seller` tinyint(1) DEFAULT 0,
  `is_referrer` tinyint(1) DEFAULT 0,
  `is_property_owner` tinyint(1) DEFAULT 0,
  `is_vehicle_owner` tinyint(1) DEFAULT 0,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `deleted_at` datetime(3) DEFAULT NULL,
  `is_deleted` tinyint(1) DEFAULT 0,
  `created_by` bigint(20) unsigned NOT NULL,
  `updated_by` bigint(20) unsigned NOT NULL,
  `alt_mobile` bigint(20) unsigned DEFAULT NULL,
  `location` longtext DEFAULT NULL,
  `source` longtext DEFAULT NULL,
  `store_id` bigint(20) unsigned NOT NULL,

  PRIMARY KEY (`id`),
  KEY `idx_customers_name` (`name`),
  KEY `fk_customers_store` (`store_id`),
  KEY `fk_customers_store_user` (`owner_id`),
  CONSTRAINT `fk_customers_owner` FOREIGN KEY (`owner_id`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_customers_store` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27037 DEFAULT CHARSET=utf8mb4

问题现象 #

表中存在与已删除外键同名的索引fk_customers_store_user，尝试执行以下语句删除该索引：

ALTER TABLE customers DROP KEY fk_customers_store_user;

或

ALTER TABLE customers DROP INDEX fk_customers_store_user;

均报错：

ERROR 1553 (HY000): Cannot drop index 'fk_customers_store_user': needed in a foreign key constraint

已删除该索引对应的外键约束，但仍无法删除索引，询问解决方法。

已尝试操作 #

• 直接删除约束和索引
• 执行SELECT CONSTRAINT_NAME FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'customers';查询当前约束名称

问题原因分析 #

从表结构可见，owner_id字段上存在另一个外键约束fk_customers_owner，该外键依赖了fk_customers_store_user索引——InnoDB要求外键字段必须有对应索引保障关联查询效率，当外键存在时，其依赖的索引无法直接删除。

方法一：先删除依赖外键，再删索引（可按需重建外键） #

1. 删除fk_customers_owner外键约束：

ALTER TABLE customers DROP FOREIGN KEY fk_customers_owner;

2. 删除目标索引：

ALTER TABLE customers DROP INDEX fk_customers_store_user;

3. 若需保留外键关联，重新创建外键（InnoDB会自动为owner_id生成新索引，也可自定义索引名）：

ALTER TABLE customers ADD CONSTRAINT fk_customers_owner FOREIGN KEY (owner_id) REFERENCES users(id);

方法二：先创建替代索引，再删除原索引 #

若不想删除外键，可先在owner_id字段创建新索引，之后InnoDB会允许删除原索引（外键会自动切换到新索引）：

1. 创建新索引：

ALTER TABLE customers ADD INDEX idx_customers_owner_id (owner_id);

2. 删除原索引：

ALTER TABLE customers DROP INDEX fk_customers_store_user;

内容的提问来源于stack exchange，提问作者Coder_H