tino tino - 7 months ago 14
SQL Question

Cannot add foreign key mysql error 1215

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

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

Answer

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

... references Ingredienti(IngredienteID, UnitaDiMisura)

According to the MySQL documentation:

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

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.