MySQL错误1822:无法添加外键约束；存在约束BUT索引的缺失索引 [英] MySQL Error 1822: Failed to add foreign key constraint; missing index for contraint BUT index exists

问题描述

我正在尝试将一个外键添加到我的排期表中，但是它失败了，但是我真的不知道为什么.外键应引用tblAircraft表中的txtAC_tag属性，该属性是主键的一部分！ 因此，将tblAircraft编入索引(主键是由idAC和txtAC_tag组成的组合键->组合的主键可能是问题吗?)并且属性的数据类型匹配.

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);

这是错误消息:

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!

推荐答案

问题在这里:

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

在这里，您以外键关系将txtAC_tag绑定到tblaircrafts表的txtAC_tag，但是在tblaircrafts中，列txtAC_tag既不是unique也不是primary，这就是它显示错误的原因.

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.

对于外键关系，要在其上创建关系的父表列必须为unique或primary，并且它们也必须具有相同的数据类型.

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 also.

要解决此问题，请使txtAC_tag列唯一.

To resolve this make txtAC_tag column unique.

这篇关于MySQL错误1822:无法添加外键约束；存在约束BUT索引的缺失索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！