MySQL报错1822：创建外键约束失败，被引用表Collection缺失索引

错误产生原因 #

MySQL中创建外键约束必须满足核心校验规则，你遇到该报错的常见原因有两个：

• 被引用字段无匹配索引：虽然你给Collection表的emp_name、amt字段加了UNIQUE约束（默认会生成唯一索引），但如果之前已经创建过不带唯一约束的Collection表，本次执行CREATE TABLE IF NOT EXISTS会直接跳过建表逻辑，旧表的两个关联字段没有有效索引，就会触发1822报错。
• 关联字段属性不匹配：如果Collection.emp_name和Employee.employee_name两个varchar字段的字符集、排序规则不一致，就算双方都有索引，也会因为索引不兼容触发该错误。

另外你的代码还有两处额外逻辑错误，后续运行也会触发报错：

• 插入顺序错误：先插入Employee记录再插入Collection记录，外键约束要求Employee的关联值必须在Collection中已经存在，顺序反了会触发外键校验失败。
• Collection插入语句语法错误：INSERT INTO Collection VALUES (1,'Bob',1000,10000, 500, tcol - amt );中直接使用字段表达式tcol - amt作为插入值是不合法的，不能在插入时引用同一条记录的其他未插入字段做运算。

修复方案 #

你可以直接使用修改后的完整代码执行：

CREATE DATABASE IF NOT EXISTS Company;
USE Company;

-- 先清理旧表避免残留结构干扰
DROP TABLE IF EXISTS Employee;
DROP TABLE IF EXISTS Collection;

CREATE TABLE IF NOT EXISTS Collection 
(
  emp_id int not null,
  emp_name varchar(60), 
  numA int ,
  tcol int , 
  amt  int ,
  acol int,
  PRIMARY KEY (emp_id),
  UNIQUE (amt),
  UNIQUE(emp_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS Employee 
(
  employee_name varchar(60) , 
  amount int , 
  PRIMARY KEY(employee_name),
  FOREIGN KEY (employee_name) REFERENCES Collection(emp_name),
  FOREIGN KEY (amount) REFERENCES Collection(amt)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 调整插入顺序，先插被引用表Collection
INSERT INTO Collection VALUES (1,'Bob',1000,10000, 500, 10000 - 500 );
-- 再插Employee，amount值和Collection的amt值保持一致
INSERT INTO Employee VALUES ('Bob',500);

-- 验证查询
SELECT * FROM Collection;
SELECT * FROM Employee;

修复点说明：

• 新增删表逻辑，彻底避免旧表残留结构的干扰
• 两张表统一指定存储引擎和字符集，避免字段属性不兼容
• 调整插入顺序，先插入被引用表的记录
• 修复Collection插入的表达式写法，直接写入运算后的结果，同时调整Employee插入的amount值，保证和Collection的amt值匹配，避免后续外键校验失败

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