mysql - 无法添加外键mysql错误1215

Question

我是 mysql 的新手，我不知道自己做错了什么......我正在使用 mysql workbench 和

$ mysql -V

mysql  Ver 14.14 Distrib 5.7.12, for Linux (x86_64) using  EditLine wrapper

我尝试索引外键但没有。

create table Prodotti(
    ProdottoID int not null auto_increment,
    Descrizione nvarchar(50) not null,
    PrezzoUnitario decimal not null,
    RicavoUnitario decimal not null,
    constraint PK_Prodotti primary key(ProdottoID)
)ENGINE=INNODB; 

create table Ingredienti(
    IngredienteID int not null auto_increment,
    Descrizione nvarchar(50) not null,
    Giacenza int not null,
    CostoUnitario decimal not null,
    UnitaDiMisura nvarchar(45) not null,
    constraint PK_Ingredienti primary key(IngredienteID)
)ENGINE=INNODB;

create table Prodotti_Ingredienti(
    ProdottoID int not null,
    IngredienteID int not null,
    Quantita decimal not null,
    UnitaDiMisura nvarchar(45) not null,
    constraint FK_Prod_Ing_prodottoid foreign key(ProdottoID) references     Prodotti(ProdottoID)
        on delete cascade
    on update cascade,  
constraint FK_Prod_Ing_ingredienteidunitamisura foreign key(IngredienteID,UnitaDiMisura) references     Ingredienti(IngredienteID,UnitaDiMisura)
        on update cascade,
    constraint PK_Prod_Ing primary key(ProdottoID,IngredienteID)
)ENGINE=INNODB;

Answer 1

根据 MySQL 手册:

InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

这意味着您需要在 Ingredienti 表中为 IngredienteID,UnitaDiMisura) 建立索引，否则它将无法工作。