Mysql – Cannot add foreign key constraint while creating table

constraintforeign keyMySQLrelational-theory

My Error Code is 1215 which finds problem with the foreign key constraint.
I know all fields are the same data types. I can't see where the problem is.
Any help would be much appreciated! Thanks!

Here's the ERD: enter image description here

and the schema:

=== TRANSACTION TABLE ===

CREATE TABLE IF NOT EXISTS `test`.`Transaction` (
  `transaction_id` SMALLINT(5) NOT NULL,
  `customer_id` SMALLINT NULL,
  `copy_id` DECIMAL(5) NOT NULL,
  `transaction_date` DATE NOT NULL,
  `transaction_amount` DECIMAL(3,2) NULL,
  `transaction_comment` VARCHAR(60) NULL,
  `previous_transaction_id` SMALLINT(5) NOT NULL,
  `transaction_type_code` VARCHAR(10) NOT NULL,
  `employee_emp_id` TINYINT NOT NULL,
  INDEX `fk_Copy_Id_idx` (`copy_id` ASC),
  INDEX `fk_Transaction_Transaction_Type1_idx` (`transaction_type_code` ASC),
  PRIMARY KEY (`transaction_id`),
  INDEX `fk_Previous_Transaction_Id_idx` (`previous_transaction_id` ASC),
  INDEX `fk_Transaction_Emp_idx` (`employee_emp_id` ASC),
  INDEX `fk_Transaction_Customer_Id_idx` (`customer_id` ASC),
  CONSTRAINT `fk_Copy_Id`
    FOREIGN KEY (`copy_id`)
    REFERENCES `test`.`Customer_Rentals` (`copy_id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Previous_Transaction_Id`
    FOREIGN KEY (`previous_transaction_id`)
    REFERENCES `test`.`Transaction` (`transaction_id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Transaction_Type_Code`
    FOREIGN KEY (`transaction_type_code`)
    REFERENCES `test`.`Transaction_Type` (`transaction_type_code`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Transaction_Emp`
    FOREIGN KEY (`employee_emp_id`)
    REFERENCES `test`.`Employee` (`emp_Id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Transaction_Customer_Id`
    FOREIGN KEY (`customer_id`)
    REFERENCES `test`.`Customer` (`custumer_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

=== CUSTOMER RENTALS === 

CREATE TABLE IF NOT EXISTS `test`.`Customer_Rentals` (
  `copy_id` DECIMAL(5) NOT NULL,
  `customer_id` SMALLINT NOT NULL,
  `movie_id` SMALLINT NULL,
  `status_code` VARCHAR(10) NOT NULL,
  `date_out` DATETIME NOT NULL,
  `date_returned` DATETIME NOT NULL,
  `amount_due` DECIMAL(4,2) NOT NULL DEFAULT 00.00,
  PRIMARY KEY (`copy_id`),
  INDEX `fk_Customer_Id_idx` (`customer_id` ASC),
  INDEX `fk_Movie_Id_idx` (`movie_id` ASC),
  INDEX `fk_Rent_Status_Code_idx` (`status_code` ASC),
  CONSTRAINT `fk_Customer_Id`
    FOREIGN KEY (`customer_id`)
    REFERENCES `test`.`Customer` (`custumer_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Movie_Id`
    FOREIGN KEY (`movie_id`)
    REFERENCES `test`.`Movie` (`movie_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Rent_Status_Code`
    FOREIGN KEY (`status_code`)
    REFERENCES `test`.`Rent_Status` (`rental_status_code`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

=== TRANSACTION TYPE TABLE === 

CREATE TABLE IF NOT EXISTS `test`.`Transaction_Type` (
  `transaction_type_code` VARCHAR(10) NOT NULL,
  `transaction_type_desc` ENUM('Payment','Refund') NULL,
  PRIMARY KEY (`transaction_type_code`),
  INDEX `transaction_type_code` (`transaction_type_code` ASC))
ENGINE = InnoDB;

=== EMPLOYEE === 

CREATE TABLE IF NOT EXISTS `test`.`Employee` (
  `emp_Id` TINYINT NOT NULL AUTO_INCREMENT,
  `emp_DoB` DATE NULL,
  `first_name` VARCHAR(45) NOT NULL,
  `last_name` VARCHAR(45) NULL,
  `hire_date` DATE NOT NULL,
  PRIMARY KEY (`emp_Id`))
ENGINE = InnoDB;

=== CUSTOMER TABLE === 

CREATE TABLE IF NOT EXISTS `test`.`Customer` (
  `custumer_id` SMALLINT NOT NULL AUTO_INCREMENT,
  `membership` VARCHAR(15) NOT NULL,
  `name` VARCHAR(20) NOT NULL,
  `surname` VARCHAR(20) NOT NULL,
  `address` VARCHAR(45) NOT NULL,
  `custumer_email` VARCHAR(45) NOT NULL,
  `dataOfBirth` DATE NULL,
  `sex` TINYTEXT NULL,
  `dateOfRegistration` DATETIME NOT NULL,
  `active` TINYINT(1) NOT NULL,
  PRIMARY KEY (`custumer_id`),
  INDEX `lastname` (`surname` ASC))
ENGINE = InnoDB;

====================== EDIT ================================

Seems that foreign key options cannot be left undefined! 

CONSTRAINT `fk_Rent_Status_Code`
    FOREIGN KEY (`status_code`)
    REFERENCES `test`.`Rent_Status` (`rental_status_code`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)

must be changed to:

CONSTRAINT `fk_Rent_Status_Code`
    FOREIGN KEY (`status_code`)
    REFERENCES `test`.`Rent_Status` (`rental_status_code`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)

Best Answer

Seems that foreign key options cannot be left undefined!

CONSTRAINT `fk_Rent_Status_Code`
    FOREIGN KEY (`status_code`)
    REFERENCES `test`.`Rent_Status` (`rental_status_code`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)

must be changed to:

CONSTRAINT `fk_Rent_Status_Code`
    FOREIGN KEY (`status_code`)
    REFERENCES `test`.`Rent_Status` (`rental_status_code`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
Related Question