mysql - 无法创建表(错误号 : 150) with added KEY

Question

我在将部分数据库添加到就绪数据库时遇到问题。它看起来像:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Table `kreker92_diplom`.`diplom_sites_users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `kreker92_diplom`.`diplom_sites_users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `idusers` INT NOT NULL,
  `idsite` INT NOT NULL,
  `datecreate` DATETIME NOT NULL,
  `dateedit` DATETIME NULL,
  PRIMARY KEY (`id`, `idsite`, `idusers`),
  INDEX `fk_diplom_sites_users_diplom_users1_idx` (`idusers` ASC),
  CONSTRAINT `fk_diplom_sites_users_diplom_users1`
    FOREIGN KEY (`idusers`)
    REFERENCES `kreker92_diplom`.`diplom_users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `kreker92_diplom`.`diplom_sites_data`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `kreker92_diplom`.`diplom_sites_data` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `idsite` INT NOT NULL,
  `siteurl` VARCHAR(200) NOT NULL,
  `mobsiteurl` VARCHAR(200) NOT NULL,
  `uridir` VARCHAR(100) NOT NULL,
  `sitetype` VARCHAR(45) NOT NULL,
  `panelstyle` VARCHAR(45) NOT NULL,
  `publish` TINYINT(1) NOT NULL,
  `multilang` TINYINT(1) NOT NULL,
  `callbtn` TINYINT(1) NOT NULL,
  `sinchronization` TIME NULL,
  PRIMARY KEY (`id`, `idsite`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `kreker92_diplom`.`diplom_sites`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `kreker92_diplom`.`diplom_sites` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `idsite` INT NOT NULL,
  `datecreate` DATETIME NOT NULL,
  `dateedit` DATETIME NULL,
  `sites_data_idsite` INT NOT NULL,
  KEY ix_diplom_sites_idsite (idsite),
  PRIMARY KEY (`id`, `idsite`),
  CONSTRAINT `fk_sites_users`
    FOREIGN KEY (`idsite`)
    REFERENCES `kreker92_diplom`.`diplom_sites_users` (`idsite`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_sites_sites_data1`
    FOREIGN KEY (`idsite`)
    REFERENCES `kreker92_diplom`.`diplom_sites_data` (`idsite`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

但是 phpMyAdmin 感到难过:

1005 - 无法创建表“kreker92_diplom.diplom_sites”(错误号:150)

abd 开始于:

-- -----------------------------------------------------
-- Table `kreker92_diplom`.`diplom_sites`
-- -----------------------------------------------------

但我已经制作了一把 key :

KEY ix_diplom_sites_idsite (idsite),

那么我做错了什么？

Answer 1

要使字段成为外键，其父字段必须定义'INDEX'。
根据有关外键约束的文档:

REFERENCES tbl_name (index_col_name,...)

在'diplom_sites'表中，

CONSTRAINT `fk_sites_users`
    FOREIGN KEY (`idsite`)
    REFERENCES `kreker92_diplom`.`diplom_sites_users` (`idsite`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,

引用的父字段'diplom_sites_users( idsite )'尚未定义'key'，它只是复合键的一部分。在其上定义一个单独的键。

CREATE TABLE IF NOT EXISTS `kreker92_diplom`.`diplom_sites_users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `idusers` INT NOT NULL,
  `idsite` INT NOT NULL,
  `datecreate` DATETIME NOT NULL,
  `dateedit` DATETIME NULL,

  INDEX `ix_users_idsite`( `idsite` ), -- <------ this was missing

  PRIMARY KEY (`id`, `idsite`, `idusers`),
  INDEX `fk_diplom_sites_users_diplom_users1_idx` (`idusers` ASC),
  CONSTRAINT `fk_diplom_sites_users_diplom_users1`
    FOREIGN KEY (`idusers`)
    REFERENCES `kreker92_diplom`.`diplom_users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

引用:

• MySQL Using FOREIGN KEY Constraints

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]

reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION