CODEWITHSUNDEEP
javamysqlhibernaterest
Anirudh Kanabar
Anirudh Kanabar

Reputation: 28

Error Code: 1215 - Cannot add foreign key constraint

I am running this Query on MySQL workbench with the latest version of MySQL installed on MacOS. My SQL Query is:

CREATE DATABASE  IF NOT EXISTS `imdb`;
USE `imdb`;


DROP TABLE IF EXISTS `rating`;



DROP TABLE IF EXISTS `media`;

CREATE TABLE `media` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(70) DEFAULT NULL,
  `year` varchar(70) DEFAULT NULL,
  `rated` varchar(70) DEFAULT NULL,
  `released` varchar(70) DEFAULT NULL,
  `runtime` varchar(70) DEFAULT NULL,
  `genre` varchar(70) DEFAULT NULL,
  `director` varchar(70) DEFAULT NULL,
  `writer` varchar(70) DEFAULT NULL,
  `actors` varchar(70) DEFAULT NULL,
  `plot` varchar(7000) DEFAULT NULL,
  `language` varchar(70) DEFAULT NULL,
  `country` varchar(70) DEFAULT NULL,
  `awards` varchar(70) DEFAULT NULL,
  `poster` varchar(270) DEFAULT NULL,
  `metascore` varchar(70) DEFAULT NULL,
  `imdb_rating` varchar(70) DEFAULT NULL,
  `imdb_votes` varchar(70) DEFAULT NULL,
  `imdb_id` varchar(70) NOT NULL,
  `type` varchar(70) DEFAULT NULL,
  `dvd` varchar(70) DEFAULT NULL,
  `box_office` varchar(70) DEFAULT NULL,
  `production` varchar(70) DEFAULT NULL,
  `website` varchar(70) DEFAULT NULL,
  `response` varchar(70) DEFAULT NULL,
  PRIMARY KEY (`id`)

);

CREATE TABLE `rating` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `source` varchar(70) DEFAULT NULL,
  `value` varchar(70) DEFAULT NULL,
  `imdb_id` varchar(70) NOT NULL,

  PRIMARY KEY (`id`),
  FOREIGN KEY (`imdb_id`)
  REFERENCES `media`(`imdb_id`)
  ON DELETE SET NULL
);

SET FOREIGN_KEY_CHECKS = 1;

The create Query on table rating is failing. I don't know why, any help would be appreciated.

The exact error response is: 

10:12:44
CREATE TABLE `rating` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `source` varchar(70) DEFAULT NULL,   `value` varchar(70) DEFAULT NULL,   `imdb_id` varchar(70) NOT NULL,    PRIMARY KEY (`id`),   FOREIGN KEY (`imdb_id`)   REFERENCES `media`(`imdb_id`)   ON DELETE SET NULL )

Error Code: 1215. Cannot add foreign key constraint
0.123 sec

I am building a Spring REST application that uses one too many relationships for building a local copy of the IMDB database or something similar.

Upvotes: 0

Views: 84

Answers (3)

monty
monty

Reputation: 1590

imdb_id is not a key on the media table - foreign keys must reference keys (or, at least, unique constraints). If imdb_id on the media table should be unique, then you could put a UNIQUE CONSTRAINT on it and will be able to reference it with a foreign key.

Also, you have on delete set null on a column that cannot be null

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522636

A foreign key in MySQL doesn't actually have to reference a primary key column, but it does have to reference a column which is unique. So, one possible fix here would be to add a unique constraint on the imdb_id column:

ALTER TABLE media ADD CONSTRAINT cstr_imdb UNIQUE (imdb_id);

Upvotes: 2

mavrk
mavrk

Reputation: 51

A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them.

'id' is the primary key in your table 'media' and not 'imdb_id'. Hence, you can't make a reference to 'imdb_id'.

Upvotes: 2

Related Questions