2

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)
3
  • 1
    I tried your case in MySQL 5.6 and it worked fine, but I had to create also tables Movie and Rent_Status. InnoDB does support NO ACTION syntax. Note that the column referenced by a foreign key must be the leftmost column in a key in its table. Jun 12, 2014 at 17:09
  • @BillKarwin, I was not aware that you could define foreign keys against partial keys, so I tried and apparently you can. However, I find the on delete (possibly on update as well) cascade action rather weird. Say I have t(x,y): (1,1),(1,2) and u(x): (1) with t p.k (x,y) and f.k from u(x) to t(x). I I delete (1,1) from t I would not have expected a row removed from u since (1,2) still exists in t. Do you know what iso/ansi states about partial key dependencies? Jun 12, 2014 at 19:52
  • 1
    @Lennart, in standard SQL, you have to use the whole key and it must be a unique or primary key. InnoDB is more permissive, allowing the referenced index to be non-unique, or allowing the referenced columns to be the left-prefix subset of an index. That's non-standard behavior. I don't recommend doing that, because you get into weird situations like a child row that references multiple rows in the parent. Down that path lies only madness! Jun 12, 2014 at 20:11

1 Answer 1

0

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)
1
  • What was the exact error you got before and for which statement exactly the error was thrown? What is the definition of Rent_Status table? You left a few important parts of the question out. Jun 15, 2014 at 18:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.