SQL错误1822：无法添加外键约束。约束缺少索引，但索引已存在

debugcn 发表于 Dev

Aphax

我正在尝试添加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中已经创建的INDEX fk_sf_file_sf_file_category1_idx（id_file_categoryASC，file_typeASC）。

有什么特殊的方法可以创建我所缺少的多字段外键？

Aphax

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。