MySQL: Error while creating foreign key, error 150 when it seems right

Question

I'have to create a database and this is MySQL code:

    drop database if EXISTS BibliotecaLello;
create database BibliotecaLello;
use BibliotecaLello; 

create table Lettore
(
    CodiceFiscale varchar(17) PRIMARY KEY not null  
);

create TABLE Tessera 
(
    CodiceLettore varchar(17),
    CodiceTessera integer PRIMARY KEY not null AUTO_INCREMENT,
    Nome varchar(10) not null,
    Cognome varchar(10) not null,
    unique(Nome, Cognome)
);

create table Autori
(
    IDAutore integer PRIMARY KEY NOT null AUTO_INCREMENT,
    Nome varchar(20) not null,
    Cognome varchar(20) not null
);

create table Libro
(
    CodiceLibro integer AUTO_INCREMENT PRIMARY KEY not null,
    IDAutore integer not null,
    Titolo varchar(20) not null
);

create table Copia
(
    CodiceLibro integer not null,
    CodiceCopia integer not null,
    unique(CodiceLibro, CodiceCopia)
);

create table Noleggio
(
    CodiceLettore varchar(17) not null,
    DataInizio date not null,
    DataFine date not null,
    CodiceLibro integer not null,
    CodiceCopia integer not null,
    unique(CodiceLettore, DataInizio, DataFine, CodiceLibro, CodiceCopia)
);

alter table Noleggio add CONSTRAINT fkNolleggioLettore
FOREIGN key (CodiceLettore)
REFERENCES `Lettore`(`CodiceFiscale`) on DELETE CASCADE on UPDATE CASCADE;

alter table Tessera add CONSTRAINT fkTesseraLettori
FOREIGN KEY (`CodiceLettore`) 
REFERENCES `Lettore`(`CodiceFiscale`) on DELETE CASCADE on UPDATE cascade;

alter table Libro add CONSTRAINT fkLibroAutori
FOREIGN KEY (IDAutore)
REFERENCES `Autori`(`IDAutore`)  on DELETE CASCADE on UPDATE cascade;

alter table `Copia` add CONSTRAINT fkCopieLibro
FOREIGN KEY (CodiceLibro) 
REFERENCES Libro(CodiceLibro)  on DELETE CASCADE on UPDATE cascade;

alter table Noleggio add CONSTRAINT fkNoleggioCopiaCopia
FOREIGN KEY (CodiceCopia) 
REFERENCES `Copia`(`CodiceCopia`) on DELETE CASCADE on UPDATE cascade;

alter table Noleggio add CONSTRAINT fkNoleggioCopiaLibro
FOREIGN KEY (CodiceLibro) 
REFERENCES `Copia`(`CodiceLibro`) on DELETE CASCADE on UPDATE cascade;

but when I execute

alter table Noleggio add CONSTRAINT fkNoleggioCopiaCopia
FOREIGN KEY (CodiceCopia) 
REFERENCES `Copia`(`CodiceCopia`) on DELETE CASCADE on UPDATE cascade;

I get

ERROR 1005 (HY000): Can't create table `BibliotecaLello`.`#sql-2f7_2c2` (errno: 150 "Foreign key constraint is incorrectly formed")

I've tried look into

SHOW ENGINE INNODB STATUS

and this is the result of the LATEST FOREIGN KEY ERROR

2019-12-02 14:37:25 75ea2340 Error in foreign key constraint of table `BibliotecaLello`.`Noleggio`:

FOREIGN KEY (CodiceCopia) 
REFERENCES `Copia`(`CodiceCopia`) on DELETE CASCADE on UPDATE cascade:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.

My teacher said me that it means that I'm altering the wrong table, but trying with both tables it still doesn't work. Can you please help me solving this problem?

Answer 1

When I run this code:

alter table Noleggio add CONSTRAINT fkNoleggioCopiaCopia
FOREIGN KEY (CodiceCopia) 
REFERENCES `Copia`(`CodiceCopia`) on DELETE CASCADE on UPDATE cascade;

It gives me error:

Failed to add the foreign key constraint. Missing index for constraint 'fkNoleggioCopiaCopia' in the referenced table 'Copia'

The problem is that, in the referenced table, column CodiceCopia is defined as the second column in the UNIQUE key:

create table Copia
(
    CodiceLibro integer not null,
    CodiceCopia integer not null,
    unique(CodiceLibro, CodiceCopia)
);

This is a documented behavior:

Foreign key constraints are subject to the following conditions and restrictions:

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order.

A simple solution is to make it the first column in the constraint, as follows:

create table Copia
(
    CodiceLibro integer not null,
    CodiceCopia integer not null,
    unique(CodiceCopia, CodiceLibro)
);

Side note: it would be good to a proper primary key on that table.

Demo on DB Fiddle