Error Code: 1215. Cannot add foreign key constraint

Question

I'm creating a DBSM on Mysql Workbench, but when Itry creating the last table i get an error because it can't add the foreign key, but i can't understand why since the child key has the same data type and collation as the parent key and the name isn't repeated.

That's the code for the two tables:

CREATE TABLE PRESTITO(
MATRICOLA_U INT,
CODICE_LIB SMALLINT(4),
DATA_P DATE,
DATA_R DATE,
N_DIP SMALLINT(2), 

PRIMARY KEY(CODICE_LIB),

FOREIGN KEY(MATRICOLA_U) REFERENCES UTENTI(MATRICOLA),
FOREIGN KEY(CODICE_LIB) REFERENCES LIBRI(CODICE_L)
);

CREATE TABLE DIPARTIMENTO(
NUMERO_DIP SMALLINT(2),
NOME_DIP VARCHAR(50) NOT NULL,
INDIRIZZO_DIP VARCHAR(50),

PRIMARY KEY(NUMERO_DIP),
FOREIGN KEY (NUMERO_DIP) REFERENCES PRESTITO(N_DIP)
);

Is there someone able to help? Thank you very much!

Answer 1

Eversy referenced column in your case N_DIP has to be indexed, which i added as KEY(N_DIP). A PRiMARY KEY is of course indexed, so as long as you stick to them you haven't a problem.

With combined keys which you can also use, you need the same number of columns in your table as there are columns in the combined key

CREATE TABLE PRESTITO(
MATRICOLA_U INT,
CODICE_LIB SMALLINT(4),
DATA_P DATE,
DATA_R DATE,
N_DIP SMALLINT(2), 
KEY (N_DIP),
PRIMARY KEY(CODICE_LIB),

FOREIGN KEY(MATRICOLA_U) REFERENCES UTENTI(MATRICOLA),
FOREIGN KEY(CODICE_LIB) REFERENCES LIBRI(CODICE_L)
);

CREATE TABLE DIPARTIMENTO(
NUMERO_DIP SMALLINT(2),
NOME_DIP VARCHAR(50) NOT NULL,
INDIRIZZO_DIP VARCHAR(50),

PRIMARY KEY(NUMERO_DIP),
FOREIGN KEY (NUMERO_DIP) REFERENCES PRESTITO(N_DIP)
);

Answer 2

I suspect you have the direction of the foreign key relationship wrong. If dipartament.numero_dip is the primary key, then you should reference it from prestito, not the other way around:

CREATE TABLE DIPARTIMENTO(
NUMERO_DIP SMALLINT(2),
NOME_DIP VARCHAR(50) NOT NULL,
INDIRIZZO_DIP VARCHAR(50),

PRIMARY KEY(NUMERO_DIP)
);

CREATE TABLE PRESTITO(
MATRICOLA_U INT,
CODICE_LIB SMALLINT(4),
DATA_P DATE,
DATA_R DATE,
N_DIP SMALLINT(2), 
PRIMARY KEY(CODICE_LIB),

FOREIGN KEY(MATRICOLA_U) REFERENCES UTENTI(MATRICOLA),
FOREIGN KEY(CODICE_LIB) REFERENCES LIBRI(CODICE_L),
FOREIGN KEY(N_DIP) REFERENCES DIPARTIMENTO(NUMERO_DIP)
);