MySQL error 1215 cannot add foreign key constraint

Question

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

Just wondering: is this exactly the script you tried? Because here you would refer to rental table before having created it — Insac, Oct 20 '16 at 16:36
Table name starts with capital letter. `FOREIGN KEY (RentalID) REFERENCES Rental (RentalID)` — Dez, Oct 20 '16 at 16:36
`REFERENCES rental (RentalID)` <---- try capitalizing rental to match the name of the table. Also, does Rental exist when you try to add the FK to Film? — WillardSolutions, Oct 20 '16 at 16:36
As Insac said, the Rental table must be created before Film table — Sefran2, Oct 20 '16 at 16:38
I just wonder about any system in which a rental is the master to a film. Seems a bit backwards. — T Gray, Oct 20 '16 at 16:39
@Sefran2 I too thought it was the capital letter of my table, however when I run the script it creates all my tables with lowercase, despite me typing them? — Josh, Oct 20 '16 at 16:41

score 0 · Accepted Answer · answered Oct 20 '16 at 17:02

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)
 );

[Using my create table functions, I create 'Customers' and 'Films' but on the right you can see that the tables are created in lowercase][1] [1]: https://i.stack.imgur.com/rwU2l.png — Josh, Oct 20 '16 at 17:09
I think that is another question, probably related to this one.. http://stackoverflow.com/q/6134006/5077154 — Insac, Oct 20 '16 at 17:13

MySQL error 1215 cannot add foreign key constraint

1 Answers1