Q:

"1215 - cannot add foreign key constraint"

I have to create a double foreign key in the "Sceglie" table, but MySQL shows me always the same error: "#1215 - Cannot add foreign key constraint ". It only happens with "Cognome_candidato" and not with "Nome_candidato".I've also tried to write FOREIGN KEY (Nome_candidato, Cognome_candidato) REFERENCES Candidato(Nome_candidato, Cognome_candidato) ON DELETE CASCADE ON UPDATE CASCADE, but it didn't create the double foreign key. How can i solve it?
create table Candidato (
Nome_candidato varchar(255),
Cognome_candidato varchar(255),
PRIMARY KEY (Nome_candidato, Cognome_candidato));

create table Sceglie (
Email varchar(255),
Nome_candidato varchar(255),
Cognome_candidato varchar(255),
PRIMARY KEY (Email, Nome_candidato, Cognome_candidato),
FOREIGN KEY (Email) REFERENCES Utente(Email)
ON DELETE CASCADE 
ON UPDATE CASCADE, 
FOREIGN KEY (Nome_candidato) REFERENCES Candidato(Nome_candidato)
ON DELETE CASCADE 
ON UPDATE CASCADE, 
FOREIGN KEY (Cognome_candidato) REFERENCES Candidato(Cognome_candidato)
ON DELETE CASCADE 
ON UPDATE CASCADE );

A:

You need an index for cognome_candidato, before a foreign key can reference that column.

