向MySQL表添加列时触发ERROR 1553索引错误的求助

• 操作场景：给mvp表添加列时执行ALTER语句，触发错误：

ERROR 1553 (HY000): Cannot drop index '<unknown key name>': needed in a foreign key constraint

• 试过的办法：给mvp表添加包含外键列的新索引、设置SET FOREIGN_KEY_CHECKS=0;后操作，均无效；甚至无法删除关联表m_strk的外键约束
• 表结构细节：
  • mvp表：主键为mvp_idtype_id，存在非唯一索引mvp_pk（包含idtype、id两列）
  • m_strk表：通过外键m_strk_fk关联mvp的idtype+id组合列
• 背景：这些表创建于MySQL8.4升级前，8.0版本允许外键关联非唯一索引，升级到8.4后所有相关操作均报错

核心原因是MySQL8.4收紧了外键规则，要求外键必须关联唯一索引或主键，原非唯一索引不符合新规范导致操作被阻断。按以下步骤操作：

1. 先给mvp表创建覆盖idtype和id的唯一索引：

CREATE UNIQUE INDEX mvp_idtype_id_unique ON mvp(idtype, id);

注意：需确保idtype+id组合在mvp表中是唯一的，否则创建会失败——外键引用的列组合必须唯一是数据库的基础规则。

2. 修改m_strk表的外键，使其关联新创建的唯一索引：

ALTER TABLE m_strk DROP FOREIGN KEY m_strk_fk;
ALTER TABLE m_strk ADD CONSTRAINT m_strk_fk FOREIGN KEY (idtype, id) REFERENCES mvp(idtype, id);

这一步MySQL会自动将外键关联到新的唯一索引，因为它优先匹配唯一键和主键。

3. 现在可正常执行添加列的操作，也可处理原非唯一索引：

-- 示例：添加需要的新列
ALTER TABLE mvp ADD COLUMN new_col VARCHAR(50);
-- 若不需要原非唯一索引，可删除
DROP INDEX mvp_pk ON mvp;

4. 验证操作结果，确保外键和索引状态正常：

SHOW CREATE TABLE m_strk;
SHOW INDEX FROM mvp;

SET FOREIGN_KEY_CHECKS=0仅临时关闭外键约束的检查，但MySQL8.4中，外键依赖的索引本身不符合规则时，即使关闭检查，也无法删除或修改依赖的索引——底层规则已收紧，必须先将外键关联到合法的唯一索引才能解除依赖。

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