时间戳

首页 > 解决方案 > 为什么我收到错误：错误 1822：无法添加外键约束？引用表中缺少约束 XXXX 的索引？

mysql - 为什么我收到错误：错误 1822：无法添加外键约束？引用表中缺少约束 XXXX 的索引？

问题描述

为什么我在创建表时不断收到以下错误？

错误：错误 1822：无法添加外键约束。引用表“PAYMENT_METHOD_TNANT_STRFRNT”中的约束“fk_PAYMENT_METHOD_MATRIX_PAYMENT_METHOD_TENT_STRFRNT1”缺少索引

我将 PAYMENT_METHOD_TNANT_STRFRNT 表的主键集定义为 PAYMENT_METHOD_MATRIX 表中的外键。

如果我and 在 PAYMENT_METHOD_MATRIX 中将 STOREFRONT TENANT` 作为 FOREIGN KEY 删除，那么它就可以工作了。

我错过了什么？

-- -----------------------------------------------------
-- Table `mydb`.`PAYMENT_METHOD`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`PAYMENT_METHOD` (
  `PAYMENT_METHODID` VARCHAR(64) NOT NULL,
  `STATUS` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`PAYMENT_METHODID`))
ENGINE = InnoDB
ROW_FORMAT = Default;


-- -----------------------------------------------------
-- Table `mydb`.`PAYMENT_METHOD_TNANT`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`PAYMENT_METHOD_TNANT` (
  `PAYMENT_METHOD_ID` VARCHAR(64) NOT NULL,
  `TENANT` VARCHAR(64) NOT NULL,
  `STATUS` VARCHAR(64) NOT NULL,
  `CREATED_DATE` TIMESTAMP NOT NULL,
  `MODIFY_DATE` TIMESTAMP NOT NULL,
  INDEX `fk_PAYMENT_METHOD_TENANT_PAYMENT_METHOD1_idx` (`PAYMENT_METHOD_ID` ASC) VISIBLE,
  PRIMARY KEY (`PAYMENT_METHOD_ID`, `TENANT`),
  CONSTRAINT `fk_PAYMENT_METHOD_TENANT_PAYMENT_METHOD1`
    FOREIGN KEY (`PAYMENT_METHOD_ID`)
    REFERENCES `mydb`.`PAYMENT_METHOD` (`PAYMENT_METHODID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`PAYMENT_METHOD_TNANT_STRFRNT`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`PAYMENT_METHOD_TNANT_STRFRNT` (
  `PAYMENT_METHOD_ID` VARCHAR(64) NOT NULL,
  `TENANT` VARCHAR(64) NOT NULL,
  `STOREFRONT` VARCHAR(64) NOT NULL,
  `STATUS` VARCHAR(64) NOT NULL,
  `CREATED_DATE` TIMESTAMP NOT NULL,
  `MODIFY_DATE` TIMESTAMP NOT NULL,
  PRIMARY KEY (`PAYMENT_METHOD_ID`, `TENANT`, `STOREFRONT`),
  INDEX `fk_PAYMENT_METHOD_TENT_STRFRNT_PAYMENT_METHOD_TENANT1_idx` (`PAYMENT_METHOD_ID` ASC, `TENANT` ASC) VISIBLE,
  CONSTRAINT `fk_PAYMENT_METHOD_TENT_STRFRNT_PAYMENT_METHOD_TENANT1`
    FOREIGN KEY (`PAYMENT_METHOD_ID` , `TENANT`)
    REFERENCES `mydb`.`PAYMENT_METHOD_TNANT` (`PAYMENT_METHOD_ID` , `TENANT`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`PAYMENT_METHOD_MATRIX`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`PAYMENT_METHOD_MATRIX` (
  `PAYMENT_METHOD_MATRIX_ID` INT NOT NULL,
  `PAYMENT_METHOD_ID` VARCHAR(64) NOT NULL,
  `TENANT` VARCHAR(45) NOT NULL,
  `STOREFRONT` VARCHAR(64) NOT NULL,
  `COUNTRY` VARCHAR(45) NOT NULL,
  `DISP_ORDER` SMALLINT NOT NULL,
  `STATUS` VARCHAR(64) NOT NULL,
  `CREATED_DATE` TIMESTAMP NOT NULL,
  `MODIFY_DATE` TIMESTAMP NOT NULL,
  PRIMARY KEY (`PAYMENT_METHOD_MATRIX_ID`, `PAYMENT_METHOD_ID`, `TENANT`, `STOREFRONT`, `COUNTRY`),
  INDEX `fk_PAYMENT_METHOD_MATRIX_PAYMENT_METHOD_TENT_STRFRNT1_idx` (`STOREFRONT` ASC, `PAYMENT_METHOD_ID` ASC, `TENANT` ASC) VISIBLE,
  CONSTRAINT `fk_PAYMENT_METHOD_MATRIX_PAYMENT_METHOD_TENT_STRFRNT1`
    FOREIGN KEY (`STOREFRONT` , `PAYMENT_METHOD_ID` , `TENANT`)
    REFERENCES `mydb`.`PAYMENT_METHOD_TNANT_STRFRNT` (`STOREFRONT` , `PAYMENT_METHOD_ID` , `TENANT`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

标签： mysqlsqlforeign-keys

解决方案

您需要在表STOREFRONT的索引fk_PAYMENT_METHOD_MATRIX_PAYMENT_METHOD_TENT_STRFRNT1_idx 中添加 ASC PAYMENT_METHOD_MATRIX。这解决了这个问题。但是，尽管它正在工作，但我建议您对 TENANT 列使用相同长度的 Varchar，以避免这两个表中出现任何进一步的问题。

Working Code:

CREATE TABLE IF NOT EXISTS PAYMENT_METHOD (
  `PAYMENT_METHODID` VARCHAR(64) NOT NULL,
  `STATUS` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`PAYMENT_METHODID`))
ENGINE = InnoDB
ROW_FORMAT = Default;


-- -----------------------------------------------------
-- Table `PAYMENT_METHOD_TNANT`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `PAYMENT_METHOD_TNANT` (
  `PAYMENT_METHOD_ID` VARCHAR(64) NOT NULL,
  `TENANT` VARCHAR(64) NOT NULL,
  `STATUS` VARCHAR(64) NOT NULL,
  `CREATED_DATE` TIMESTAMP NOT NULL,
  `MODIFY_DATE` TIMESTAMP NOT NULL,
  INDEX `fk_PAYMENT_METHOD_TENANT_PAYMENT_METHOD1_idx` (`PAYMENT_METHOD_ID` ASC) VISIBLE,
  PRIMARY KEY (`PAYMENT_METHOD_ID`, `TENANT`),
  CONSTRAINT `fk_PAYMENT_METHOD_TENANT_PAYMENT_METHOD1`
    FOREIGN KEY (`PAYMENT_METHOD_ID`)
    REFERENCES `PAYMENT_METHOD` (`PAYMENT_METHODID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

✓

✓

-- -----------------------------------------------------
-- Table `PAYMENT_METHOD_TNANT_STRFRNT`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `PAYMENT_METHOD_TNANT_STRFRNT` (
  `PAYMENT_METHOD_ID` VARCHAR(64) NOT NULL,
  `TENANT` VARCHAR(64) NOT NULL,
  `STOREFRONT` VARCHAR(64) NOT NULL,
  `STATUS` VARCHAR(64) NOT NULL,
  `CREATED_DATE` TIMESTAMP NOT NULL,
  `MODIFY_DATE` TIMESTAMP NOT NULL,
  PRIMARY KEY (`PAYMENT_METHOD_ID`, `TENANT`, `STOREFRONT`),
  INDEX `fk_PAYMENT_METHOD_TENT_STRFRNT_PAYMENT_METHOD_TENANT1_idx` (`STOREFRONT` ASC,`PAYMENT_METHOD_ID`  ASC, `TENANT` ASC) VISIBLE,
  CONSTRAINT `fk_PAYMENT_METHOD_TENT_STRFRNT_PAYMENT_METHOD_TENANT1`
    FOREIGN KEY (`PAYMENT_METHOD_ID` , `TENANT`)
    REFERENCES `PAYMENT_METHOD_TNANT` (`PAYMENT_METHOD_ID` , `TENANT`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

✓

-- -----------------------------------------------------
-- Table `PAYMENT_METHOD_MATRIX`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `PAYMENT_METHOD_MATRIX` (
  `PAYMENT_METHOD_MATRIX_ID` INT NOT NULL,
  `PAYMENT_METHOD_ID` VARCHAR(64) NOT NULL,
  `TENANT` VARCHAR(45) NOT NULL,
  `STOREFRONT` VARCHAR(64) NOT NULL,
  `COUNTRY` VARCHAR(45) NOT NULL,
  `DISP_ORDER` SMALLINT NOT NULL,
  `STATUS` VARCHAR(64) NOT NULL,
  `CREATED_DATE` TIMESTAMP NOT NULL,
  `MODIFY_DATE` TIMESTAMP NOT NULL,
  PRIMARY KEY (`PAYMENT_METHOD_MATRIX_ID`, `PAYMENT_METHOD_ID`, `TENANT`, `STOREFRONT`, `COUNTRY`),
  INDEX `fk_PAYMENT_METHOD_MATRIX_PAYMENT_METHOD_TENT_STRFRNT1_idx` (`STOREFRONT` ASC, `PAYMENT_METHOD_ID` ASC, `TENANT` ASC) VISIBLE,
  CONSTRAINT `fk_PAYMENT_METHOD_MATRIX_PAYMENT_METHOD_TENT_STRFRNT1`
    FOREIGN KEY (`STOREFRONT` , `PAYMENT_METHOD_ID` , `TENANT`)
    REFERENCES `PAYMENT_METHOD_TNANT_STRFRNT` (`STOREFRONT` , `PAYMENT_METHOD_ID` , `TENANT`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

✓

db<>在这里摆弄

推荐阅读