MySQL FOREIGN KEY assistance

Question

I'm working on a database assignment and have run into this error.

The database should look like...

Legend: **Primary Key** *Foreign Key

.

Movies (**title, year**, length, genre, *studioName, *producerID) 
StarsIn(***movieTitle, *movieYear, *starName**) 
MovieStar(**name**, birthdate, address, gender) 
MovieMaker(**ID**, name, address)
Studio (**name**, address, *presidentID)

And my code looks like this...

CREATE TABLE Movies (
title varchar(50),
year int,
length int,
genre varchar(50),
studioName varchar(50),
producerID varchar(50),
PRIMARY KEY (title,year)
FOREIGN KEY (studioName) REFERENCES Studio(name),
FOREIGN KEY (producerID) REFERENCES MovieMaker(ID),
);

CREATE TABLE StarsIn (
movieTitle varchar(50),
movieYear int,
starName varchar(50),
PRIMARY KEY (movieTitle,movieYear,starName),
FOREIGN KEY (movieTitle) REFERENCES Movies(title),
FOREIGN KEY (movieYear) REFERENCES Movies(year),
FOREIGN KEY (starName) REFERENCES MovieStar(name)
);

CREATE TABLE MovieStar (
name varchar(50),
birthdate int,
address varchar(50),
gender varchar(50),
PRIMARY KEY (name)
);

CREATE TABLE MovieMaker (
ID varchar(50),
name varchar(50),
address varchar(50),
PRIMARY KEY (ID)
);

CREATE TABLE Studio (
name varchar(50),
address varchar(50),
presidentID varchar(50),
PRIMARY KEY (name),
FOREIGN KEY (presidentID) REFERENCES MovieMaker(ID)
);

However, I get quite a few errors stating that the syntax for my foreign keys are off. Any chance someone could lend a helping hand?

Answer 1

Tables referencing other tables via foreign keys may only be defined after the referenced tables already exist. You must therefore reorder your tables so that those which are referenced in FOREIGN KEY constraints get created first.

Additionally, the foreign keys referencing Movies.year and Movies.title are going to fail because you do not have indices defined on those columns individually. You do have them as a composite PRIMARY KEY, but they need their own indices.

/* MovieStar, MovieMaker are referenced by other tables
   but have no FKs of their own, so create them first */
CREATE TABLE MovieStar (
  name varchar(50),
  birthdate int,
  address varchar(50),
  gender varchar(50),
  PRIMARY KEY (name)
);

CREATE TABLE MovieMaker (
  ID varchar(50),
  name varchar(50),
  address varchar(50),
  PRIMARY KEY (ID)
);

/* Studio can be created next, referencing only MovieMaker */
CREATE TABLE Studio (
  name varchar(50),
  address varchar(50),
  presidentID varchar(50),
  PRIMARY KEY (name),
  FOREIGN KEY (presidentID) REFERENCES MovieMaker(ID)
);

/* Movies references 2 of the above */
CREATE TABLE Movies (
  title varchar(50),
  year int,
  length int,
  genre varchar(50),
  studioName varchar(50),
  producerID varchar(50),
  /* A comma was missing here... */
  PRIMARY KEY (title,year),
  FOREIGN KEY (studioName) REFERENCES Studio(name),
  FOREIGN KEY (producerID) REFERENCES MovieMaker(ID),
  /* Add individual indices on Movies. Omitting this would result in errno 150 */
  INDEX (year),
  INDEX (title)
);

CREATE TABLE StarsIn (
  movieTitle varchar(50),
  movieYear int,
  starName varchar(50),
  PRIMARY KEY (movieTitle,movieYear,starName),
  FOREIGN KEY (movieTitle) REFERENCES Movies(title),
  FOREIGN KEY (movieYear) REFERENCES Movies(year),
  FOREIGN KEY (starName) REFERENCES MovieStar(name)
);

Here is a demo of the whole thing building properly: http://sqlfiddle.com/#!2/eaf70c