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:
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)
Movie
andRent_Status
. InnoDB does supportNO ACTION
syntax. Note that the column referenced by a foreign key must be the leftmost column in a key in its table.