errno: 150 "Foreign key constraint is incorrectly formed - mysql

Question

I am trying to create a database for a school project. I am getting the error specified in the title, and cannot figure out how to resolve it. I am not very experienced in mySQL but apparently the error is vague and can be a number of thing. It happens when I try to input the showing table.

`CREATE TABLE Complex(
    name varchar(50) not null, 
    streetNum integer, 
    streetName varchar(50),
    city varchar(50),
    province varchar(50),
    postalCode char(6),
    numTheatres integer,
    primary key(name)
);

CREATE TABLE Theatre(
    complexName varchar(50) not null,
    theatreNum integer not null,
    maxSeats integer,
    screenSize varchar(6),
    foreign key (complexName) references Complex(name),
    primary key (complexName, theatreNum)
);

CREATE TABLE Supplier(
    compName varchar(50) not null,
    streetName varchar(50),
    streetNum varchar(50),
    city varchar(50),
    province varchar(50),
    postalCode char(6),
    phone char(10),
    contactFName varchar(10),
    contactLName varchar(10),
    primary key (compName)
);

CREATE TABLE Movie(
    title varchar(100) not null,
    runningTime integer,
    rating varchar(4),
    synopsis varchar(500),
    director varchar(100),
    prodComp varchar(100),
    supplierName varchar(100),
    startDate date,
    endDate date,
    foreign key (supplierName) references Supplier(compName),
    primary key (title)
);



Create Table Actor(
    fName varchar(50) not null,
    lName varchar(50) not null,
    primary key (fName,lName)
);

create table Stars
(
    fName varchar(100) not null references Actor(fName),
    lName varchar(100) not null references Actor(lName),
    title varchar(100) not null references Movie(title),
    primary key (fName, lName, title)
);

CREATE TABLE Account(
    accountNum integer not null AUTO_INCREMENT,
    password varchar(50) not null,
    fName varchar(50),
    lName varchar(50),
    phone char(10),
    email varchar(50),
    creditCard varchar(15),
    cardExpiry char(4),
    primary key (accountNum)
);

CREATE TABLE Showing(
    complexName varchar(50) not null,
    title varchar(100) not null,
    theatreNum integer not null,
    startTime time not null,
    seatsAvailable integer,
    foreign key (complexName) references Theatre(complexName),
    foreign key (title) references Movie(title),
    foreign key (theatreNum) references Theatre(theatreNum),
    primary key (startTime)
);

CREATE TABLE Review(
    title varchar(100) not null,
    ID integer not null,
    score integer,
    primary key (ID),
    foreign key (title) references Movie(title)
);

CREATE TABLE Reserved(
    accountNum integer not null,
    complexName varchar(50) not null,
    theatreNum integer not null,
    movieTitle varchar(50) not null,
    startTime time not null,
    ticketsNum integer,
    foreign key (accountNum) references Account(accountNum),
    foreign key (complexName) references Showing(compName),
    foreign key (theatreNum) references Showing(theatreNum),
    foreign key (movieTitle) references Showing(movieTitle),
    foreign key (startTime) references Showing(startTime),
    primary key (accountNum, complexName, theatreNum, movieTitle, startTime)
)

;`

Simplify your question. We don't need (or want) all that code, just the part that matters to reproduce the problem. (I.e. remove columns and tables that aren't needed to get the error.) — jarlh, Mar 26 '18 at 16:56
MySQL errors almost always indicate where the error occurred; why omit that information from your question? — Uueerdo, Mar 26 '18 at 17:19
See the answer I gave your classmate. https://stackoverflow.com/a/49483909/1797579 Then tell him that on stackoverflow it is proper to accept answers to questions, at which point we could close your as a duplicate. — Willem Renzema, Mar 26 '18 at 18:53
Possible duplicate of [MySQL "Foreign key constraint is incorretly formed"](https://stackoverflow.com/questions/49483651/mysql-foreign-key-constraint-is-incorretly-formed) — Willem Renzema, Mar 27 '18 at 19:21

score 0 · Accepted Answer · answered Mar 26 '18 at 18:47

This is one of the most common misunderstandings about foreign keys for beginners.

CREATE TABLE Theatre(
    complexName varchar(50) not null,
    theatreNum integer not null,
    primary key (complexName, theatreNum)

CREATE TABLE Showing(
    complexName varchar(50) not null,
    theatreNum integer not null,
    foreign key (complexName) references Theatre(complexName),
    foreign key (theatreNum) references Theatre(theatreNum),
    ...

When you reference a table with a multi-column primary key, your foreign key must be multi-column as well. Don't declare two foreign key constraints. Declare one foreign key constraint with the same columns as the primary key it references.

CREATE TABLE Showing(
    complexName varchar(50) not null,
    theatreNum integer not null,
    foreign key (complexName, theatreNum) references Theatre(complexName, theatreNum),
    ...

For more tips on foreign key, like a checklist of what needs to be true, see the top answer on this question: MySQL Creating tables with Foreign Keys giving errno: 150

errno: 150 "Foreign key constraint is incorrectly formed - mysql

1 Answers1