MySQL Error 1822: Failed to add foreign key constraint; missing index for contraint BUT index exists

Question

I am trying to add an foreign key to my flightschedule table but it fails, but I do not really know why. The foreign key should reference the txtAC_tag attribute from the tblAircraft table which is part of the Primary key! So the tblAircraft is indexed (the primary key is a combined key which consists of idAC and txtAC_tag -> could a combined Primary key be the problem?) and the attribute's datatype do match.

Here are my table declarations and the foreign key declarations:

create table if not exists tblAircrafts(
idAC       int not null auto_increment,
txtAC_tag  varchar(255) not null,
txtAC_type varchar(255) not null,
primary key(idAC, txtAC_tag));

create table if not exists tblFlightSchedule(
ID int not null auto_increment,
datDate date,
txtFrom varchar(255),
txtTo   varchar(255),
txtFlight varchar(255),
numFlight_time_decimal decimal(4,2),
txtAC_tag varchar(255) not null,
txtAC_type varchar(255) not null,
numSeatCapacity int unsigned,
numLoad int unsigned, -- auslastung 
numDirt decimal(20,19),
numTotalFlightTime decimal(50,5),
numCumDirt decimal(20,15),
primary key(ID));

alter table tblflightschedule
add foreign key(txtAC_tag) references tblaircrafts(txtAC_tag);

And here is the ERROR message:

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

Any suggestions? I appreciate any kind of help you can give me, thank you!

Answer 1

The issue is here:

add foreign key(txtAC_tag) references tblaircrafts(txtAC_tag);

here you are binding txtAC_tag to txtAC_tag of tblaircrafts table in a foreign key relationship but in tblaircrafts the column txtAC_tag is neither unique nor primary that's why it is showing error.

For foreign key relationship, the parent table column on which you are creating relation must be unique or primary and they must have the same datatype and size also.

To resolve this make txtAC_tag column unique.

Answer 2

It seems you have created Composite Primary Key for the table tblAircrafts.

If you wanted to add the composite key reference to the table tblflightschedule, you need to use the below syntax:

alter table tblflightschedule
add foreign key ('int Column', txtAC_tag) references tblaircrafts **(idAC, txtAC_tag);**

And you have to refer two columns for adding foreign key ('int Column', txtAC_tag).

So you can either add one more column in your tblflightschedule table or drop one column from tblaircrafts table.