MySQL -- "Cannot add foreign key constraint"

Question

I am trying to create a series of tables for a hospital database, and whenever i try to run the code, i get an error saying "Error Code 1215: Cannot add foreign key constraint." I've done a lot of research to see what i've done wrong, but i cant figure it out. What's wrong with my foreign keys? Here are the relevant tables. Thanks.

create table Patient(
num int(8),
Fname varchar(20),
Lname varchar(20),
SSN char(11),
dateofBirth date,   
primary key(num)
);

create table Department(
Description varchar(250),
DepCode char(3),
DepName varchar(15),
Supervisor int(8),  
building char(6),
primary key(DepCode),
constraint fk_supervisor foreign key(Supervisor) references Employee(ID)
);  

create table Employee(
ID int(8),
Fname varchar(20),
Lname varchar(20),
SSN char(11),
Specialty varchar(15),
dateofHire date,
primary key(ID)
);

user2864740 · Accepted Answer · 2014-10-14 00:05:07Z

3

A table (or column) that does not exist cannot be schema referenced.

Create the Employee table before the Department table - or add the constraint later, after all tables have been created, with ALTER TABLE .. ADD FOREIGN KEY.

Example corrected ordering:

create table Patient ..
create table Employee ..
create table Department ..

edited Oct 14, 2014 at 0:05

answered Oct 13, 2014 at 23:58

user2864740

62.5k15 gold badges159 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL -- "Cannot add foreign key constraint"

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related