MySQL Error 1822：添加外键约束失败，缺失索引问题求助

问题描述 #

执行添加外键的ALTER语句时，触发如下错误：

ERROR: Error 1822: Failed to add the foreign key constraint. Missing index for constraint 'fk_Facility_employee_access_Facility_Bookings2' in the referenced table 'Facility_Bookings'

参照表Facility_Bookings已设置主键，但依然提示缺失索引，无法添加外键约束。

原因分析 #

Facility_Bookings的主键定义为：

PRIMARY KEY (`Booking_id`,`Booked_Entry_Date`,`Booked_Exit_Date`),

而待添加的外键fk_Facility_employee_access_Facility_Bookings2仅关联Booked_Entry_Date和Booked_Exit_Date两个字段。InnoDB对外键的要求是：关联的字段组合在参照表中必须存在以该组合开头的索引（包括主键或普通索引）。由于主键的第一个字段是Booking_id，Booked_Entry_Date和Booked_Exit_Date只是主键的后续部分，无法被外键约束识别为有效索引，因此触发错误。

解决方法 #

方案1：给参照表添加符合要求的索引 #

直接在Facility_Bookings表中创建以Booked_Entry_Date、Booked_Exit_Date开头的独立索引，满足外键约束的索引要求：

ALTER TABLE `Companies`.`Facility_Bookings` 
ADD INDEX `idx_booked_dates` (`Booked_Entry_Date`, `Booked_Exit_Date`);

执行完上述语句后，再重新运行原来的ALTER语句添加外键约束即可。

方案2：修改外键关联完整主键字段 #

若业务逻辑允许，外键应关联参照表的完整主键，这样可以直接利用已有的主键索引：

1. 先给Facility_employee_access表添加Booking_id关联字段：

ALTER TABLE `Companies`.`Facility_employee_access` 
ADD COLUMN `Facility_Bookings_Booking_id` INT NOT NULL;

2. 创建对应字段的索引：

ALTER TABLE `Companies`.`Facility_employee_access` 
ADD INDEX `idx_booking_full` (`Facility_Bookings_Booking_id`, `Facility_Bookings_Booked_Entry_Date`, `Facility_Bookings_Booked_Exit_Date`);

3. 修改外键约束语句，关联完整主键：

ALTER TABLE `Companies`.`Facility_employee_access` 
ADD CONSTRAINT `fk_Facility_employee_access_Facility_Bookings2`
  FOREIGN KEY (`Facility_Bookings_Booking_id`, `Facility_Bookings_Booked_Entry_Date`, `Facility_Bookings_Booked_Exit_Date`)
  REFERENCES `Companies`.`Facility_Bookings` (`Booking_id`, `Booked_Entry_Date`, `Booked_Exit_Date`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

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