Can not add foreign key constraint, How should i fix this error?

Question

I want to add foreign key constraint of the idDept in the Staff, But it tells me that "Error Code 1215(HY000)".

The following is my SQL code, exported by workbench.

I guess it may be caused by INDEX idDept_idx (idDept ASC)， but deleting it in my code is not work,too.

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;

Possible duplicate of [MySQL : ERROR 1215 (HY000): Cannot add foreign key constraint](https://stackoverflow.com/questions/18930084/mysql-error-1215-hy000-cannot-add-foreign-key-constraint) — Dinesh Ghule, Jul 27 '18 at 03:18

score 0 · Answer 1 · answered Jul 27 '18 at 03:33

Remove ZEROFILL run below updated query :

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;

Can not add foreign key constraint, How should i fix this error?

1 Answers1