mysql "Cannot add foreign key constraint"

Question

I am trying to run the following SQL (on MySQL server) create statements:

Create table if not exists Employees (id integer primary key, name char(40) not null, department char(40) not null, salary int not null, phone char(40) not null)
Create table if not exists Managers (mid integer primary key, name char(40) not null, department char(40) not null, salary integer not null, phone char(40) not null)
Create table if not exists Departments (did integer primary key, dname char(40) not null, numberofworkers integer not null, manager char(40) not null)
Create table if not exists works_in (id integer, did integer, primary key (id, did), foreign key Employees(id) references Employees, foreign key Departments(did) references Departments)
Create table if not exists Management (mid integer, did integer, primary key (mid, did), foreign key Managers(mid) references Managers, foreign key Departments(did) references Departments)

All is good except the last two lines, I am getting:

ERROR 1215 (HY000): Cannot add foreign key constraint

I tried running SHOW ENGINE INNODB STATUS; to see where the problem is, and it gave:

Error in foreign key constraint of table project/works_in:
foreign key Employees(id) references Employees, foreign key Departments(did) references Departments):
Syntax error close to:
, foreign key Departments(did) references Departments)

But this isn't helpful in anyway. I tried checking other similar questions but most of them suggested that the columns should be of same type, which is already the case here.

Why am I getting this error?

Gordon Linoff · Accepted Answer · 2018-06-10 12:18:00Z

2

The syntax for the foreign key declaration must include the keys being referenced in the "other" table:

foreign key (did) references Departments (did)
foreign key (mid) references Managers(mid)
foreign key Departments (did) references Departments (did)

When creating a table, I strongly encourage you to put every column on a separate line:

Create table if not exists works_in (
    id integer,
    did integer,
    primary key (id, did),
    foreign key (id) references Employees(id),
    foreign key (did) references Departments(did)
);

How can you read just a long unbroken stream of column definitions?

answered Jun 10, 2018 at 12:18

Gordon Linoff

1.3m62 gold badges706 silver badges857 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