Josh Josh - 1 month ago 7
MySQL Question

MySQL error 1215 cannot add foreign key constraint

sorry i know there are loads about this but I don't understand them!

CREATE TABLE Customer (
CustomerID INT UNSIGNED NOT NULL AUTO_INCREMENT,
LastName VARCHAR(50),
FirstName VARCHAR(50),
Address VARCHAR(255),
City VARCHAR(50),
PRIMARY KEY (CustomerID)
);

CREATE TABLE Film (
FilmID INT UNSIGNED NOT NULL AUTO_INCREMENT,
FilmName VARCHAR(100),
FilmRelease DATE,
FilmDirector VARCHAR(100),
RentalID int unsigned,
PRIMARY KEY (FilmID),
FOREIGN KEY (RentalID)
REFERENCES rental (RentalID)
);

CREATE TABLE Rental (
RentalID INT UNSIGNED NOT NULL AUTO_INCREMENT,
Price VARCHAR(25),
DateIn DATETIME,
DateOut DATETIME,
PRIMARY KEY (RentalID),
);


And yes my indenations may be off because of the way I copied and pasted it.

The error is the FOREIGN KEY RENTALID, it works if I use the foreign key as CustomerID from Customer

Answer

I've tested the script on RexTester and indeed there were two issues:

  • the foreign key on Film table was referring the Rental table before it was created
  • in the Rental table there was a "," after the definition of the primary key

Here is the final script (the initial drop are there just to allow for easy "drop and create" testing)

 drop TABLE if exists  Film ;
 drop TABLE if exists  Customer;
 drop TABLE if exists  Rental ;

 CREATE TABLE  Customer (
   CustomerID INT UNSIGNED NOT NULL AUTO_INCREMENT,
   LastName VARCHAR(50),
   FirstName VARCHAR(50),
   Address VARCHAR(255),
   City VARCHAR(50),
 PRIMARY KEY (CustomerID)
 );



 CREATE TABLE  Rental (
   RentalID INT UNSIGNED NOT NULL AUTO_INCREMENT,
   Price VARCHAR(25),
   DateIn DATETIME,
   DateOut DATETIME,
   PRIMARY KEY (RentalID)
 );


 CREATE TABLE  Film (
   FilmID INT UNSIGNED NOT NULL AUTO_INCREMENT,
   FilmName VARCHAR(100),
   FilmRelease DATE,
   FilmDirector VARCHAR(100),
   RentalID int unsigned,

   PRIMARY KEY (FilmID), 

   FOREIGN KEY (RentalID) REFERENCES  Rental (RentalID)
 );
Comments