MySQL删列报索引被外键依赖错误 无法定位依赖项求助

对业务表执行列删除操作时遇到异常，原计划先删除关联外键，再删除外键指向的列，执行的SQL语句如下：

ALTER TABLE resources drop foreign key fk_res_to_addr;
ALTER TABLE resources drop column address_id;

外键约束删除操作执行成功，但删除address_id列时抛出错误：Cannot drop index 'fk_res_to_addr': needed in a foreign key constraint。

首先尝试定位仍依赖该索引的对象，执行如下系统表查询语句：

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME, 
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
    TABLE_SCHEMA = 'some_db' AND
    REFERENCED_TABLE_NAME = 'resources';

查询结果为空，未找到关联外键。之后尝试执行如下语句临时禁用外键检查，操作完成后再重新启用，该方案无效：

SET FOREIGN_KEY_CHECKS=0;

待解决疑问：

• 还有什么方法可以定位依赖该索引的对象？
• 是否存在遗漏的排查方向？

补充：当前表定义 #

当前表中已不存在address_id关联的外键，仅残留对应索引，完整建表语句如下：

create table resources
(
id                                     bigint auto_increment primary key,
created                                bigint           not null,
lastModified                           bigint           not null,
uuid                                   varchar(255)     not null,
description                            longtext         null,
internalName                           varchar(80)      null,
publicName                             varchar(80)      not null,
origin                                 varchar(80)      null,
archived                               bigint unsigned  null,
contact_id                             bigint           null,
colorClass                             varchar(80)      null,
address_id                             bigint           null,
url                                    mediumtext       null,
constraint uuid
    unique (uuid),
constraint FK_contact_id
    foreign key (contact_id) references users (id)
)
charset = utf8;

create index fk_res_to_addr on resources (address_id);

create index idx_resources_archived on resources (archived);

create index idx_resources_created on resources (created);

之前的排查存在两个明显遗漏：

1. 外键查询条件设置过窄，只查了「其他表引用resources表」的外键，没有覆盖resources作为子表引用其他表的外键，也没有匹配约束名、列名的关联关系，很容易漏结果。
2. SET FOREIGN_KEY_CHECKS=0 只会关闭外键数据一致性校验，不会跳过DDL执行时的元数据依赖检查，对这个场景完全无效。

按以下步骤操作即可解决问题：

• 第一步：扩大查询范围，查全库所有关联address_id列、fk_res_to_addr约束的外键，不要加父表过滤条件

SELECT *
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'some_db'
  AND (CONSTRAINT_NAME = 'fk_res_to_addr' OR COLUMN_NAME = 'address_id');

• 第二步：如果上面的查询还是返回空，直接查InnoDB底层存储的外键元数据，这里存的是实际生效的元数据，不会出现可视化表结构展示和实际状态不一致的问题：

-- 查关联resources表的所有外键条目
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN 
WHERE FOR_NAME = 'some_db/resources' OR REF_NAME = 'some_db/resources';

-- 查这些外键关联的列信息
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS
WHERE ID IN (
  SELECT ID FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN 
  WHERE FOR_NAME = 'some_db/resources' OR REF_NAME = 'some_db/resources'
);

• 第三步：把查询到的残留外键全部删除，注意如果外键属于其他表（即其他表引用了resources的address_id列），要到对应表上执行删除外键操作，不要在resources表上执行。
• 第四步：所有关联外键清理完成后，先手动删除残留索引，再删除目标列即可：

ALTER TABLE resources DROP INDEX fk_res_to_addr;
ALTER TABLE resources DROP COLUMN address_id;

如果以上操作还是查不到外键，大概率是之前删除外键的会话没有提交事务，导致元数据变更未持久化，直接断开当前数据库连接重连后，再执行第四步操作即可。

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