mysql - 无法添加外键约束，我该如何解决此错误？

我想在Staff中添加idDept的外键约束，但是它告诉我“错误代码1215（HY000）”。

以下是我的SQL代码，由工作台导出。

我猜可能是由INDEX idDept_idx（idDept ASC）引起的，但是在我的代码中删除它也是行不通的。

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';

CREATE SCHEMA IF NOT EXISTS `Hospitalization` DEFAULT CHARACTER SET utf8 ;
USE `Hospitalization` ;

CREATE TABLE IF NOT EXISTS `Hospitalization`.`Dept` (
  `idDept` INT(2) ZEROFILL NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `director` INT(5) NOT NULL,
  `location` INT(3) NOT NULL,
  PRIMARY KEY (`idDept`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `Hospitalization`.`Staff` (
  `idStaff` INT(5) ZEROFILL NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `sex` CHAR(2) NOT NULL,
  `contact` VARCHAR(45) NOT NULL,
  `role` VARCHAR(45) NOT NULL,
  `password` VARCHAR(45) NOT NULL DEFAULT '123456',
  `idDept` INT(2) NOT NULL,
  PRIMARY KEY (`idStaff`),
  INDEX `idDept_idx` (`idDept` ASC),
  CONSTRAINT `idDept`
    FOREIGN KEY (`idDept`)
    REFERENCES `Hospitalization`.`Dept` (`idDept`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

最佳答案

删除ZEROFILL在更新后的查询下面运行：

CREATE TABLE IF NOT EXISTS `Hospitalization`.`Dept` (
  `idDept` INT(2) NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `director` INT(5) NOT NULL,
  `location` INT(3) NOT NULL,
  PRIMARY KEY (`idDept`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `Hospitalization`.`Staff` (
  `idStaff` INT(5) ZEROFILL NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `sex` CHAR(2) NOT NULL,
  `contact` VARCHAR(45) NOT NULL,
  `role` VARCHAR(45) NOT NULL,
  `password` VARCHAR(45) NOT NULL DEFAULT '123456',
  `idDept` INT(2) NOT NULL,
  PRIMARY KEY (`idStaff`),
  INDEX `idDept_idx` (`idDept` ASC),
  CONSTRAINT `idDept`
    FOREIGN KEY (`idDept`)
    REFERENCES `Hospitalization`.`Dept` (`idDept`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;