Error Code: 1215. Cannot add foreign key constraint - while creating db in mysql workbench

Question

I created a model for this db in mysql workbench, but now when running the script to create the tables I'm getting: "Error Code: 1215. Cannot add foreign key constraint".

The error pops up when it gets to the payment table at the end of the script. I've been looking at it for the past day, making tweaks here and there based on solutions I've found online, but nothing works.

Any help is very much appreciated.

-- MySQL Script generated by MySQL Workbench
-- Thu Dec 7 14:01:08 2017
-- Model: New Model Version: 1.0
-- MySQL Workbench Forward Engineering
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';
-- -----------------------------------------------------
-- Schema game_rental
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `game_rental` ;
-- -----------------------------------------------------
-- Schema game_rental
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `game_rental` ;
USE `game_rental` ;
-- -----------------------------------------------------
-- Table `game_rental`.`platform`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `game_rental`.`platform` ;
CREATE TABLE IF NOT EXISTS `game_rental`.`platform` (
 `platform_id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(25) NOT NULL,
 PRIMARY KEY (`platform_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `game_rental`.`member`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `game_rental`.`member` ;
CREATE TABLE IF NOT EXISTS `game_rental`.`member` (
 `member_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
 `first_name` VARCHAR(45) NOT NULL,
 `last_name` VARCHAR(45) NOT NULL,
 `email` VARCHAR(50) NOT NULL,
 PRIMARY KEY (`member_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `game_rental`.`game`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `game_rental`.`game` ;
CREATE TABLE IF NOT EXISTS `game_rental`.`game` (
 `game_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
 `title` VARCHAR(255) NOT NULL,
 `publisher` VARCHAR(45) NOT NULL,
 `rental_duration` TINYINT UNSIGNED NOT NULL DEFAULT 3,
 `rental_rate` DECIMAL(4,2) NOT NULL DEFAULT 5.00,
 `replacement_cost` DECIMAL(5,2) NOT NULL DEFAULT 50.00,
 PRIMARY KEY (`game_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `game_rental`.`game_platform`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `game_rental`.`game_platform` ;
CREATE TABLE IF NOT EXISTS `game_rental`.`game_platform` (
 `game_id` SMALLINT UNSIGNED NOT NULL,
 `platform_id` TINYINT UNSIGNED NOT NULL,
 PRIMARY KEY (`game_id`, `platform_id`),
 CONSTRAINT `fk_game_id_plt`
 FOREIGN KEY (`game_id`)
 REFERENCES `game_rental`.`game` (`game_id`)
 ON DELETE RESTRICT
 ON UPDATE CASCADE,
 CONSTRAINT `fk_platform_id`
 FOREIGN KEY (`platform_id`)
 REFERENCES `game_rental`.`platform` (`platform_id`)
 ON DELETE RESTRICT
 ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
CREATE INDEX `fk_platform_id` ON `game_rental`.`game_platform` (`platform_id` ASC);
-- -----------------------------------------------------
-- Table `game_rental`.`inventory`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `game_rental`.`inventory` ;
CREATE TABLE IF NOT EXISTS `game_rental`.`inventory` (
 `inventory_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
 `game_id` SMALLINT UNSIGNED NOT NULL,
 PRIMARY KEY (`inventory_id`),
 CONSTRAINT `fk_game_id_inv`
 FOREIGN KEY (`game_id`)
 REFERENCES `game_rental`.`game` (`game_id`)
 ON DELETE RESTRICT
 ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
CREATE INDEX `idx_fk_game_id` ON `game_rental`.`inventory` (`game_id` ASC);
-- -----------------------------------------------------
-- Table `game_rental`.`rental`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `game_rental`.`rental` ;
CREATE TABLE IF NOT EXISTS `game_rental`.`rental` (
 `rental_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
 `rental_date` DATETIME NOT NULL,
 `inventory_id` MEDIUMINT UNSIGNED NOT NULL,
 `member_id` SMALLINT UNSIGNED NOT NULL,
 `return_date` DATETIME NULL DEFAULT NULL,
 `staff_id` TINYINT UNSIGNED NOT NULL,
 PRIMARY KEY (`rental_id`),
 CONSTRAINT `fk_inventory_id`
 FOREIGN KEY (`inventory_id`)
 REFERENCES `game_rental`.`inventory` (`inventory_id`)
 ON DELETE RESTRICT
 ON UPDATE CASCADE,
 CONSTRAINT `fk_member_id`
 FOREIGN KEY (`member_id`)
 REFERENCES `game_rental`.`member` (`member_id`)
 ON DELETE RESTRICT
 ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
CREATE INDEX `idx_fk_inventory_id` ON `game_rental`.`rental` (`inventory_id` ASC);
CREATE INDEX `idx_fk_member_id_rent` ON `game_rental`.`rental` (`member_id` ASC);
-- -----------------------------------------------------
-- Table `game_rental`.`payment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `game_rental`.`payment` ;
CREATE TABLE IF NOT EXISTS `game_rental`.`payment` (
 `payment_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
 `member_id` SMALLINT UNSIGNED NOT NULL,
 `rental_id` INT UNSIGNED NOT NULL,
 `amount` DECIMAL(5,2) NOT NULL,
 `payment_date` DATETIME NOT NULL,
 PRIMARY KEY (`payment_id`),
 CONSTRAINT `fk_rental_id`
 FOREIGN KEY (`rental_id`)
 REFERENCES `game_rental`.`rental` (`rental_id`)
 ON DELETE SET NULL
 ON UPDATE CASCADE,
 CONSTRAINT `fk_member_id`
 FOREIGN KEY (`member_id`)
 REFERENCES `game_rental`.`member` (`member_id`)
 ON DELETE RESTRICT
 ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
CREATE INDEX `idx_fk_member_id_pay` ON `game_rental`.`payment` (`member_id` ASC);
CREATE INDEX `fk_rental_id` ON `game_rental`.`payment` (`rental_id` ASC);
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Answer 1

The problem is with your first Foreign Key in payment:

CONSTRAINT `fk_rental_id`
 FOREIGN KEY (`rental_id`)
 REFERENCES `game_rental`.`rental` (`rental_id`)
 ON DELETE SET NULL
 ON UPDATE CASCADE,

Basically, what you're saying is that, when there's a DELETE from the game_rental.rental, all referenced rental_id in the payment table will be set to null

From the Documentation (https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html):

SET NULL: Delete or update the row from the parent table, and set the foreign key column or columns in the child table to NULL. Both ON DELETE SET NULL and ON UPDATE SET NULL clauses are supported.

If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL.

The last line should clarify everything, you have rental_id set as NOT NULL in payment, so there's the problem. You should either remove the NOT NULL restriction in the rental_id from your payment table, or remove the "ON DELETE SET NULL".

Finally; after that you'll have another error:

Can't write; duplicate key in table 'payment'

Because the name

CONSTRAINT `fk_member_id`

is used in game_rental.rental first, you'll need to change the name in the payment table for that CONSTRAINT and that'll be it.

• Brian McNamara: Thank you so much for this! I was starting to go slightly insane from staring at the code. Lesson learned :)