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)
  • 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. – Bill Karwin Jun 12 '14 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? – Lennart Jun 12 '14 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! – Bill Karwin Jun 12 '14 at 20:11
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)
| improve this answer | |
  • 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. – ypercubeᵀᴹ Jun 15 '14 at 18:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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