0

Error Code: 1215. Cannot add foreign key constraint

I'm not sure wher I am getting this issue, here are the two tables in question, any help would be much appreciated!

CREATE TABLE IF NOT EXISTS `PHL_db`.`Session` (
  `year` INT NOT NULL,
  `season` VARCHAR(50) NOT NULL,
  `division` VARCHAR(25) NOT NULL,
  PRIMARY KEY (`year`, `season`, `division`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `PHL_db`.`Team` (
  `name` VARCHAR(100) NOT NULL DEFAULT 'Free Agent',
  `Session_season` VARCHAR(50) NOT NULL,
  `Session_year` INT NOT NULL,
  `Session_division` VARCHAR(25) NOT NULL,
  PRIMARY KEY (`name`, `Session_season`, `Session_year`, `Session_division`),
    FOREIGN KEY (`Session_season` , `Session_year` , `Session_division`)
    REFERENCES `PHL_db`.`Session` ( `year`, `season` , `division`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
user3643284
  • 7
  • 4
  • This error usually happens when a child table refers to records in a parent table which do not exist. In your case, it might happen if `Team` refers to `Session` parent records which no longer exist. – Tim Biegeleisen Jan 24 '19 at 00:29
  • Oh ok, I haven't had any inserts into the database yet so there are no records, would you know of a fix for this? – user3643284 Jan 24 '19 at 00:32
  • Fix the order in your `FOREIGN KEY` or `REFERENCES` clause so that session and year have the same position in both. – sticky bit Jan 24 '19 at 00:41
  • I was thinking that was an issue but wasn't 100% – user3643284 Jan 24 '19 at 00:54

2 Answers2

0

you are trying to make primary keys as foreign keys wherein primary keys cannot be used as foreign keys on the same table.

Jim Beam
  • 67
  • 1
  • 1
  • 6
0
FOREIGN KEY (`Session_season` , `Session_year` , `Session_division`)
REFERENCES `PHL_db`.`Session` ( `year`, `season` , `division`)

Should be

FOREIGN KEY (`Session_year`, `Session_season` , `Session_division`)
REFERENCES `PHL_db`.`Session` ( `year`, `season` , `division`)

The columns of the foreign key must match the columns of the referenced primary key. They must be the same:

  • Number of columns
  • Order of columns (this is the one you got wrong)
  • Data types

You might also like to read this checklist on foreign key issues that I helped contribute to: MySQL Creating tables with Foreign Keys giving errno: 150

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828