MySQL Workbench Forward Engineer Error 1215: Cannot add foreign key constraint

Question

When I execute this script to create 2 tables, where the STORE column in the CUSTOMER table references the ID column in the USERS table (both columns are INT):

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 `part_finder` DEFAULT CHARACTER SET utf8 ;
USE `part_finder` ;

-- -----------------------------------------------------
-- Table `part_finder`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `part_finder`.`users` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `account` MEDIUMINT(7) UNSIGNED NOT NULL COMMENT 'Account organisation user belongs to',
  `name` VARCHAR(32) NULL DEFAULT NULL,
  `passenc` VARCHAR(32) NULL DEFAULT NULL,
  `email` VARCHAR(55) NULL DEFAULT NULL,
  `rank` DECIMAL(1,0) NULL DEFAULT '0',
  `ip_reg` VARCHAR(15) NULL DEFAULT NULL,
  `ip_visit` VARCHAR(15) NULL DEFAULT NULL,
  `dtreg` INT(11) NOT NULL,
  `dtvisit` INT(11) NOT NULL,
  `visits` SMALLINT(5) UNSIGNED NULL DEFAULT '0',
  `pass` VARCHAR(25) NULL DEFAULT NULL,
  `make_filter_on` TINYINT(1) NULL DEFAULT FALSE,
  `brand_filter_on` TINYINT(1) NULL DEFAULT FALSE,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 3;


-- -----------------------------------------------------
-- Table `part_finder`.`customer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `part_finder`.`customer` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `store` INT(10) NOT NULL,
  `name` VARCHAR(32) NULL,
  `address` VARCHAR(45) NULL,
  `address_2` VARCHAR(45) NULL,
  `city` VARCHAR(15) NULL,
  `state` TINYINT UNSIGNED NOT NULL,
  `zip` CHAR(5) NULL,
  `phone` VARCHAR(15) NULL,
  `website` VARCHAR(45) NULL,
  `email` VARCHAR(55) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_customer_users_idx` (`store` ASC),
  CONSTRAINT `fk_customer_users`
    FOREIGN KEY (`store`)
    REFERENCES `part_finder`.`users` (`id`)
    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;

I get this error:

ERROR: Error 1215: Cannot add foreign key constraint

-- -----------------------------------------------------
-- Table `part_finder`.`customer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `part_finder`.`customer` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `store` INT(10) NOT NULL,
  `name` VARCHAR(32) NULL,
  `address` VARCHAR(45) NULL,
  `address_2` VARCHAR(45) NULL,
  `city` VARCHAR(15) NULL,
  `state` TINYINT UNSIGNED NOT NULL,
  `zip` CHAR(5) NULL,
  `phone` VARCHAR(15) NULL,
  `website` VARCHAR(45) NULL,
  `email` VARCHAR(55) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_customer_users_idx` (`store` ASC),
  CONSTRAINT `fk_customer_users`
    FOREIGN KEY (`store`)
    REFERENCES `part_finder`.`users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

SQL script execution finished: statements: 6 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

I'm using InnoDB, I've checked the data types of my foreign key columns and I've made sure I'm using a primary key in the referenced table.

Any ideas?

score 8 · Accepted Answer · edited Dec 26 '19 at 17:54

Perhaps the columns in the parent tables are INT UNSIGNED?

They need to be exactly the same data type in both tables.

Foreign Key Constraints

Reasons that you may get foreign key constraint error:

You are trying to reference a nonexistent key on the target table. Make sure that it is a key on the other table (it can be a primary or unique key),
The types of the columns are not the same (exception is the column on the referecing table can be nullable).

score 0 · Answer 2 · answered Apr 07 '14 at 06:30

Column definitions must match in both parent and child tables.

Parent

CREATE TABLE IF NOT EXISTS `part_finder`.`users` (

  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, -- <----- this is unsigned

  ...
  PRIMARY KEY (`id`))

Child

CREATE TABLE IF NOT EXISTS `part_finder`.`customer` (
...

`store` INT(10) NOT NULL, -- <----------- this is signed

...
  INDEX `fk_customer_users_idx` (`store` ASC),
  CONSTRAINT `fk_customer_users`
    FOREIGN KEY (`store`)
    REFERENCES `part_finder`.`users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)

Change:

`store` INT(10) NOT NULL, -- <----------- this is signed

To:

`store` INT(10) UNSIGNED NOT NULL, -- <---- now this too is unsigned

Refer to: MySQL: Foregin Key Constaints

MySQL Workbench Forward Engineer Error 1215: Cannot add foreign key constraint

2 Answers2

Linked