MySQL Workbench 错误 1005 无法创建表

Question

所以我对MySQL非常陌生，但我想到目前为止我已经掌握了相当多的知识。我正在努力创建表PROJECT。经过我自己的研究，我几乎确信这与我在表中的键有关。但我不明白问题出在哪里或如何解决。

下面的 SQL 查询创建项目的所有表。我不确定问题所在，因为我现在只得到该项目的错误代码。任何有关导致此问题的原因以及如何在代码中纠正此问题的见解和建议将不胜感激。

CREATE TABLE IF NOT EXISTS `ebrasi1db`.`employee` (
  `ssn` VARCHAR(10) NOT NULL,
  `fname` VARCHAR(45) NULL DEFAULT NULL,
  `minit` VARCHAR(1) NULL DEFAULT NULL,
  `lname` VARCHAR(45) NULL DEFAULT NULL,
  `bdate` DATE NULL DEFAULT NULL,
  `address` VARCHAR(45) NULL DEFAULT NULL,
  `sex` VARCHAR(1) NULL DEFAULT NULL,
  `salary` INT(11) NULL DEFAULT NULL,
  `superssn` VARCHAR(10) NULL DEFAULT NULL,
  `dno` INT(11) NULL DEFAULT NULL,
  PRIMARY KEY (`ssn`),
  INDEX `superssn_idx` (`superssn` ASC),
  INDEX `dno_idx` (`dno` ASC),
  CONSTRAINT `superssn`
    FOREIGN KEY (`superssn`)
    REFERENCES `ebrasi1db`.`employee` (`ssn`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `dno`
    FOREIGN KEY (`dno`)
    REFERENCES `ebrasi1db`.`department` (`dnumber`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)

 ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `ebrasi1db`.`department`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ebrasi1db`.`department` (
  `dnumber` INT(11) NOT NULL,
  `dname` VARCHAR(45) NULL DEFAULT NULL,
  `mgrssn` VARCHAR(10) NULL DEFAULT NULL,
  `mgrstartdate` DATE NULL DEFAULT NULL,
  PRIMARY KEY (`dnumber`),
  INDEX `mgrssn_idx` (`mgrssn` ASC),
  CONSTRAINT `mgrssn`
    FOREIGN KEY (`mgrssn`)
    REFERENCES `ebrasi1db`.`employee` (`ssn`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `ebrasi1db`.`dept_locations`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ebrasi1db`.`dept_locations` (
  `dnumber` INT(11) NOT NULL,
  `dlocation` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`dnumber`, `dlocation`),
  CONSTRAINT `dnumber`
    FOREIGN KEY (`dnumber`)
    REFERENCES `ebrasi1db`.`department` (`dnumber`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `ebrasi1db`.`project`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ebrasi1db`.`project` (
  `pnumber` INT(11) NOT NULL,
  `pname` VARCHAR(45) NULL,
  `plocation` VARCHAR(45) NULL,
  `dnum` INT(11) NOT NULL,
  PRIMARY KEY (`pnumber`),
  INDEX `dnum_idx` (`dnum` ASC),
  INDEX `plocation_idx` (`plocation` ASC),
  CONSTRAINT `dnum`
    FOREIGN KEY (`dnum`)
    REFERENCES `ebrasi1db`.`department` (`dnumber`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `plocation`
    FOREIGN KEY (`plocation`)
    REFERENCES `ebrasi1db`.`dept_locations` (`dlocation`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ebrasi1db`.`works_on`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ebrasi1db`.`works_on` (
  `essn` VARCHAR(10) NOT NULL,
  `pno` INT NOT NULL,
  `hours` DECIMAL(5,2) NULL,
  PRIMARY KEY (`essn`, `pno`),
  INDEX `pno_idx` (`pno` ASC),
  CONSTRAINT `works_on_essn`
    FOREIGN KEY (`essn`)
    REFERENCES `ebrasi1db`.`employee` (`ssn`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `pno`
    FOREIGN KEY (`pno`)
    REFERENCES `ebrasi1db`.`project` (`pnumber`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ebrasi1db`.`dependent`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ebrasi1db`.`dependent` (
  `essn` VARCHAR(10) NOT NULL,
  `dependent_name` VARCHAR(45) NOT NULL,
  `sex` VARCHAR(1) NULL,
  `bdate` DATE NULL,
  `relation` VARCHAR(45) NULL,
  PRIMARY KEY (`essn`, `dependent_name`),
  CONSTRAINT `dependent_essn`
    FOREIGN KEY (`essn`)
    REFERENCES `ebrasi1db`.`employee` (`ssn`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Answer 1

您的问题本质上在于您试图创建对非键列的约束(因为引用的表有一个双键)。

在您的表项目中，您有:

CONSTRAINT `plocation`
FOREIGN KEY (`plocation`)
REFERENCES `dept_locations` (`dlocation`)
ON DELETE NO ACTION
ON UPDATE NO ACTION

dept_locations.dlocation 不是一个单独的键(主键)，因此您无法创建该约束。

您需要对引用表中的两个键进行约束，如下所示:

CONSTRAINT `plocation`
FOREIGN KEY (`pnumber`, `plocation`)
REFERENCES `dept_locations` (`dnumber`, `dlocation`)
ON DELETE NO ACTION
ON UPDATE NO ACTION

在这种情况下，您不需要第一个约束 dnum，因为它已在 dept_locations 表中引用。

另请注意，您的表 department 和 employee 将不会被创建，因为一个表引用另一个表，因此您需要先创建没有约束的表，然后应用约束，例如:

ALTER TABLE `employee` add 
CONSTRAINT `dno`
FOREIGN KEY (`dno`)
REFERENCES `department` (`dnumber`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

对于department表及其对employee的约束也是如此