mysql - SQL 错误 1822 : Failed to add the foreign key constaint. 缺少约束索引但索引存在

Question

我正在尝试添加包含 2 列的外键。

这是引用外键的表的 DDL:

CREATE TABLE IF NOT EXISTS `sf_file_category` (
  `id_file_category`        INT                         NOT NULL AUTO_INCREMENT,
  `name`                    VARCHAR(45)
                            CHARACTER SET 'latin1'
                            COLLATE 'latin1_general_ci' NOT NULL,
  `file_type`               ENUM('document', 'image', 'video', 'archive')
                            CHARACTER SET 'latin1'
                            COLLATE 'latin1_general_ci' NULL,
  `id_file_category_parent` INT UNSIGNED                NULL,
  PRIMARY KEY (`id_file_category`),
  INDEX `fk_sf_file_category_sf_file_category1_idx` (`id_file_category_parent` ASC),
  INDEX `fk_sf_file_category_sf_file_idx` (`id_file_category` ASC, `file_type` ASC)
)
  ENGINE = InnoDB;

拥有外键的表的DDL:

CREATE TABLE IF NOT EXISTS `sf_file` (
  `id_file`           INT UNSIGNED                NOT NULL AUTO_INCREMENT,
  `fullpath`          VARCHAR(100)
                      CHARACTER SET 'latin1'
                      COLLATE 'latin1_general_ci' NOT NULL,
  `basename`          VARCHAR(45)
                      CHARACTER SET 'latin1'
                      COLLATE 'latin1_general_ci' NOT NULL,
  `accesskey`         CHAR(8)
                      CHARACTER SET 'latin1'
                      COLLATE 'latin1_general_ci' NOT NULL,
  `file_type`         ENUM('document', 'image', 'video', 'archive')
                      CHARACTER SET 'latin1'
                      COLLATE 'latin1_general_ci' NULL,
  `name`              VARCHAR(45)
                      CHARACTER SET 'latin1'
                      COLLATE 'latin1_general_ci' NULL,
  `description`       VARCHAR(255)
                      CHARACTER SET 'latin1'
                      COLLATE 'latin1_general_ci' NULL,
  `id_aircraft_image` SMALLINT UNSIGNED           NULL,
  `id_aircraft`       SMALLINT UNSIGNED           NULL,
  `id_file_category`  INT UNSIGNED                NULL,
  PRIMARY KEY (`id_file`),
  INDEX `fk_sf_file_sf_file_category1_idx` (`id_file_category` ASC, `file_type` ASC),
  INDEX `fk_sf_file_sf_aircraft1_idx` (`id_aircraft` ASC),
  INDEX `fk_sf_file_sf_aircraft2_idx` (`id_aircraft_image` ASC)
)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = latin1
  COLLATE = latin1_general_ci;

尝试执行以下外键语法:

ALTER TABLE `sf_file` 
ADD CONSTRAINT `fk_sf_file_sf_file_category1`
  FOREIGN KEY (`id_file_category` , `file_type`)
  REFERENCES `sf_file_category` (`id_file_category` , `file_type`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

但我收到此错误:错误:错误 1822:无法添加外键约束。引用表“sf_file_category”中缺少约束“fk_sf_file_sf_file_category1”的索引。

我假设他指的是已经在表 sf_file_category 中创建的索引 fk_sf_file_sf_file_category1_idx (id_file_category ASC, file_type ASC)。

是否有任何特定的方法可以创建我缺少的多字段外键？

Answer 1

就像@Pankaj Pandey提到的那样，我的字段声明不完全匹配，id_file_category在一个表上为NULL，在另一个表上为NOT NULL将阻止外键创建。