创建带FOREIGN KEY的新表时为何出现MySQL错误1822？

错误原因 #

• 外键列顺序不匹配：你定义外键时，用(AbilityName, AgentName)引用abilities表的(AgentName, AbilityName)，列顺序完全相反，MySQL无法匹配对应关系。
• 被引用表缺少对应索引：abilities表的主键是复合主键(PlayerId, AgentName, AbilityName)，复合索引遵循左前缀生效原则，AgentName和AbilityName不是主键的左前缀，因此MySQL无法利用主键索引满足外键约束的查询需求，需要为这两个列单独创建唯一索引。

解决方案 #

方案一：修正外键顺序并添加索引

先创建abilities表时，添加针对(AgentName, AbilityName)的唯一索引：

CREATE TABLE `valorant`.`abilities` (
`PlayerId` INT NOT NULL,
`AgentName` VARCHAR(45) NOT NULL,
`AbilityName` VARCHAR(45) NOT NULL,
`KeyBind` VARCHAR(45) NULL,
`Cost` INT NULL,
PRIMARY KEY (`PlayerId`, `AgentName`, `AbilityName`),
UNIQUE INDEX `idx_agent_ability` (`AgentName`, `AbilityName`)
);

再创建abilitystats表时，调整外键列顺序与被引用表一致，同时修正主键（避免单个PlayerId只能存储一条统计记录的不合理限制）：

CREATE TABLE `valorant`.`abilitystats` (
`PlayerId` INT NOT NULL,
`AgentName` VARCHAR(45) NOT NULL,
`AbilityName` VARCHAR(45) NOT NULL,
`EnemiesAffected` INT NULL,
`AlliesAffected` INT NULL,
CONSTRAINT `FK_abilitystats_abilities`
FOREIGN KEY (`AgentName`, `AbilityName`)
REFERENCES `abilities` (`AgentName`, `AbilityName`),
PRIMARY KEY (`PlayerId`, `AgentName`, `AbilityName`)
);

方案二：引用完整复合主键

如果业务上要求abilitystats的每条记录与abilities的记录一一对应，可直接引用abilities的完整复合主键，此时无需额外创建索引：

CREATE TABLE `valorant`.`abilities` (
`PlayerId` INT NOT NULL,
`AgentName` VARCHAR(45) NOT NULL,
`AbilityName` VARCHAR(45) NOT NULL,
`KeyBind` VARCHAR(45) NULL,
`Cost` INT NULL,
PRIMARY KEY (`PlayerId`, `AgentName`, `AbilityName`)
);

CREATE TABLE `valorant`.`abilitystats` (
`PlayerId` INT NOT NULL,
`AgentName` VARCHAR(45) NOT NULL,
`AbilityName` VARCHAR(45) NOT NULL,
`EnemiesAffected` INT NULL,
`AlliesAffected` INT NULL,
CONSTRAINT `FK_abilitystats_abilities`
FOREIGN KEY (`PlayerId`, `AgentName`, `AbilityName`)
REFERENCES `abilities` (`PlayerId`, `AgentName`, `AbilityName`),
PRIMARY KEY (`PlayerId`, `AgentName`, `AbilityName`)
);

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