数据库迁移报错：无法添加RegistrationsInvoices外键约束求解决

AI 小北

2026-6-13

问题：MySQL迁移时外键约束创建失败

我正在用MySQL Workbench把MS-Access数据库迁移到MySQL，迁移时报错：

Failed to add the foreign key constraint. Missing index for constraint 'RegistrationsInvoices' in the referenced table 'registrations'

以下是MySQL Workbench生成的建表脚本：

CREATE TABLE IF NOT EXISTS `EBS`.`Registrations` (
  `RegistrationID` INT(10) NOT NULL,
  `StudentID` INT(10) NULL,
  `CourseID` INT(10) NULL,
  `Discount` DOUBLE NULL,
  `Invoiced` TINYINT(1) NOT NULL,
  `Sessions` INT(10) NULL,
  PRIMARY KEY (`RegistrationID`),
  INDEX `NewRegistrationID` (`RegistrationID` ASC) VISIBLE,
  CONSTRAINT `CoursesRegistrations`
    FOREIGN KEY (`CourseID`)
    REFERENCES `EBS`.`Courses` (`CourseID`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `StudentsRegistrations`
    FOREIGN KEY (`StudentID`)
    REFERENCES `EBS`.`Students` (`StudentID`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT);


 CREATE TABLE IF NOT EXISTS `EBS`.`Invoices` (
  `InvoiceID` INT(10) NOT NULL,
  `OldInvoice#` VARCHAR(255) NULL,
  `InvoiceDate` DATETIME(6) NULL,
  `AccountID` INT(10) NULL,
  `RegistrationID1` INT(10) NULL,
  `Amount1` DOUBLE NULL,
  `RegistrationID2` INT(10) NULL,
  `Amount2` INT(10) NULL,
  `RegistrationID3` INT(10) NULL,
  `Amount3` INT(10) NULL,
  `RegistrationID4` INT(10) NULL,
  `Amount4` INT(10) NULL,
  `RegistrationID5` INT(10) NULL,
  `Amount5` INT(10) NULL,
  `AmountPaid` INT(10) NULL,
  `AmountTotal` INT(10) NULL,
  `Paid` TINYINT(1) NOT NULL,
  `Void` TINYINT(1) NOT NULL,
  PRIMARY KEY (`InvoiceID`),
  INDEX `AmountPaid` (`AmountPaid` ASC) VISIBLE,
  INDEX `NewInvoiceID` (`InvoiceID` ASC) VISIBLE,
  INDEX `NewRegistrationID` (`RegistrationID1` ASC) VISIBLE,
  INDEX `Paid` (`Paid` ASC) VISIBLE,
  INDEX `Void` (`Void` ASC) VISIBLE,
  CONSTRAINT `AccountsInvoices`
    FOREIGN KEY (`AccountID`)
    REFERENCES `EBS`.`Accounts` (`AccountID`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `RegistrationsInvoices`
    FOREIGN KEY (`RegistrationID1` , `RegistrationID2` , `RegistrationID3` , `RegistrationID4` , `RegistrationID5`)
    REFERENCES `EBS`.`Registrations` (`RegistrationID` , `RegistrationID` , `RegistrationID` , `RegistrationID` , `RegistrationID`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT);

我已经查过Stack Overflow的类似问题，确认没有拼写错误、主键已定义，还把Invoices表中所有RegistrationIDx字段改成NOT NULL，和Registrations表对应字段定义完全一致，但问题还是没解决，求排查思路。

排查思路

核心问题：复合外键的逻辑完全错误
你创建的RegistrationsInvoices是一个5字段的复合外键，要求Invoices表的RegistrationID1到RegistrationID5的组合，必须匹配Registrations表中5个RegistrationID组成的复合索引——但Registrations表只有单个RegistrationID的主键索引，根本不存在这种复合索引，这完全不符合MySQL外键的设计规则。

正确的结构修正方案（推荐）
Access可能允许这种冗余的“一对多”存储，但关系型数据库的标准设计是拆分出中间关联表，比如InvoiceRegistrations，结构如下：

CREATE TABLE IF NOT EXISTS `EBS`.`InvoiceRegistrations` (
  `InvoiceID` INT(10) NOT NULL,
  `RegistrationID` INT(10) NOT NULL,
  `Amount` INT(10) NULL,
  PRIMARY KEY (`InvoiceID`, `RegistrationID`),
  FOREIGN KEY (`InvoiceID`) REFERENCES `EBS`.`Invoices`(`InvoiceID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  FOREIGN KEY (`RegistrationID`) REFERENCES `EBS`.`Registrations`(`RegistrationID`) ON DELETE RESTRICT ON UPDATE RESTRICT
);

然后删除Invoices表中的RegistrationID1-RegistrationID5、Amount1-Amount5字段，用中间表关联发票和报名记录，既符合数据库范式，也能彻底解决外键错误。

临时应急方案（不推荐长期使用）
如果暂时不想改结构，可以删除当前的复合外键，给每个RegistrationIDx单独建外键：

CONSTRAINT `RegistrationsInvoices1`
  FOREIGN KEY (`RegistrationID1`)
  REFERENCES `EBS`.`Registrations` (`RegistrationID`)
  ON DELETE RESTRICT
  ON UPDATE RESTRICT,
CONSTRAINT `RegistrationsInvoices2`
  FOREIGN KEY (`RegistrationID2`)
  REFERENCES `EBS`.`Registrations` (`RegistrationID`)
  ON DELETE RESTRICT
  ON UPDATE RESTRICT
-- 依次给RegistrationID3到RegistrationID5创建独立外键

但这种冗余存储会增加数据维护成本，容易出现数据不一致，仅作为临时过渡方案。

额外检查点
确认Registrations和Invoices表的存储引擎是InnoDB（MyISAM不支持外键），可以用SHOW CREATE TABLE EBS.Registrations;查看。如果是MyISAM，执行ALTER TABLE EBS.Registrations ENGINE=InnoDB;修改引擎，另一张表同理。

内容的提问来源于stack exchange，提问作者Bob-it

数据库迁移报错：无法添加RegistrationsInvoices外键约束求解决

排查思路 #

排查思路