无法添加外键mysql错误1215

I'm pretty new to mysql, and I don't know what I'm doing wrong... I'm using mysql workbench and

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

$ mysql -V

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

I tried indexing the foreign keys but nothing.

我尝试索引外键但没有。

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;

2 个解决方案

#1

The problem is the referenced key, which is a composite one:

问题是引用的键,它是一个复合键:

... references Ingredienti(IngredienteID, UnitaDiMisura)

According to the MySQL documentation:

根据MySQL文档:

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan.

MySQL需要外键和引用键上的索引,以便外键检查可以很快并且不需要表扫描。

You can create such an index using the following statement:

您可以使用以下语句创建此类索引:

CREATE INDEX id_index ON Ingredienti (IngredienteID, UnitaDiMisura);

However, you should be careful as to what this FK really implements. Since the composite key (IngredienteID, UnitaDiMisura) is not the PK of table Ingredienti, nor is it a unique index, you might end up in multiple references to Ingredienti table for the (IngredienteID, UnitaDiMisura) value.

但是,你应该小心这个FK真正实现了什么。由于复合密钥(IngredienteID,UnitaDiMisura)不是表Ingredienti的PK,也不是唯一索引,因此您可能最终会在(IngredienteID,UnitaDiMisura)值中多次引用Ingredienti表。

#2

According to MySQL Manual:

根据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.

InnoDB允许外键引用任何索引列或列组。但是,在引用的表中,必须有一个索引,其中引用的列被列为相同顺序的第一列。

This means that you need to have an index for IngredienteID,UnitaDiMisura) in table Ingredienti, or it will not work.

这意味着您需要在表Ingredienti中获得IngredienteID,UnitaDiMisura的索引,否则它将无效。

#1