尝试创建外键时在 MySQL 中出现错误 1822 [英] Getting error 1822 in MySQL when trying to create a foreign key

问题描述

我在创建外键时遇到错误.错误是 1822，它说如下:

I am getting an error when creating a foreign key. The error is 1822 and it says the following:

错误代码:1822.无法添加外键约束.引用表配方"中缺少约束Changes_ibfk_4"的索引

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

我读过类似的问题，问题似乎是，我们需要在其上创建外键的列与引用的列的数据类型不同.

I have read similar question and the problem seems to be, that the column that we need to create a foreign key on, does not have same datatype as the column that is referenced to.

但是，我不知道这两列彼此有何不同.两者都是主键，具有相同的名称和类型 varchar(255).以下是 MySQL 代码.

However, I have no idea how the two columns are different to one another. Both are primary keys, with same name and type varchar(255). Below is the MySQL code.

两个版本"列之间有区别吗?或者是其他问题?

Is there a difference between the two "version" columns? Or is the problem something else?

alter table Recipe drop column version;
alter table Changes drop column version;
alter table Recipe add column version varchar(255);
alter table Changes add column version varchar(255);
alter table Recipe drop primary key, add primary key(recipeID, version);
alter table Changes drop primary key, add primary key(newRecipeID, version);
alter table Changes add foreign key(version) references Recipe(version);

推荐答案

在引用表中，必须有一个索引，其中外键列列在同一顺序的第一列 dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html 在您的示例中，您在更改表的配方中没有这样的键更改添加外键(版本)引用配方(版本

In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html in your example you have no such key in recipe for alter table Changes add foreign key(version) references Recipe(version

这篇关于尝试创建外键时在 MySQL 中出现错误 1822的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！