Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint 'project_ibfk_1' in the referenced table 'Works'

Question

Not sure why getting this error

CREATE TABLE Works(
SSN int,
ProjName varchar(255),
ProjNum int,
DeptNum int,
PRIMARY KEY (SSN,ProjName,ProjNum),
FOREIGN KEY (SSN) REFERENCES Employee(SSN),
FOREIGN KEY (DeptNum) REFERENCES Department(DeptNum)
);

CREATE TABLE Project(
ProjName varchar(255),
ProjNum int,
ProjDesc varchar(255),
PRIMARY KEY (ProjName,ProjNum),
FOREIGN KEY (ProjName) REFERENCES Works(ProjName),
FOREIGN KEY (ProjNum) REFERENCES Works(ProjNum)
);

Error

Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint 'project_ibfk_1' in the referenced table 'Works'

select * from Employee where Address = 'new york'
in (select * from Employee where DOB= '01012012');

select * from Employee where Address = 'new york'
not in (select * from Employee where DOB= '010120120');

Error Code: 1241. Operand should contain 1 column(s)

PRIMARY KEY (ProjName,ProjNum) and FOREIGN KEY (ProjName) and FOREIGN KEY (ProjNum) — Raiana Salmin Zaman, Mar 22 '20 at 01:30
Table Works needs a Primary Key for ProjName and ProjNum columns in order to be able to use those columns in the foreign key creation in Project table. — Anthony McGrath, Mar 22 '20 at 02:21
updated: CREATE TABLE Works( SSN int, ProjName varchar(255), ProjNum int, DeptNum int, PRIMARY KEY (SSN,ProjName,ProjNum), FOREIGN KEY (SSN) REFERENCES Employee(SSN), FOREIGN KEY (DeptNum) REFERENCES Department(DeptNum) ); still getting same error — Raiana Salmin Zaman, Mar 22 '20 at 02:26

score 0 · Answer 1 · answered Mar 22 '20 at 02:42

When you create a foreign key, you must match all the columns of the primary key in the table you reference. If the referenced table has two columns in its primary key, your foreign key must have two columns.

Creating a separate foreign key for each column is not the same.

You might also like to read this checklist for foreign keys: https://stackoverflow.com/a/4673775/20860

score 0 · Answer 2 · edited Aug 25 '20 at 11:20

you might try following statement.

CREATE TABLE Works(
SSN int,
ProjName varchar(255),
ProjNum int,
DeptNum int,
PRIMARY KEY (SSN,ProjName,ProjNum),
FOREIGN KEY (SSN) REFERENCES Employee(SSN),
FOREIGN KEY (DeptNum) REFERENCES Department(DeptNum)
);

CREATE TABLE Project(
ProjName varchar(255),
ProjNum int,
ProjDesc varchar(255),
PRIMARY KEY (ProjName,ProjNum),
FOREIGN KEY (ProjNum, ProjName) REFERENCES Works(ProjNum, ProjName)
);

just change:

FOREIGN KEY (ProjName) REFERENCES Works(ProjName),
FOREIGN KEY (ProjNum) REFERENCES Works(ProjNum)

to

FOREIGN KEY (ProjNum, ProjName) REFERENCES Works(ProjNum, ProjName)

Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint 'project_ibfk_1' in the referenced table 'Works'

2 Answers2