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!

• J.D.: Please note I removed the tag sql-server-2012 from your post, which is a completely different database system (referring to Microsoft's SQL Server product) from MySQL. Please only tag database systems related to your question. (If this was a mistake, then feel free to re-add it.)

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

• Lennart - Slava Ukraini: I'm kind of surprised that this actually works, but apparently, it does. Even 8.0 and MariaDB 10.5 seems to accept a foreign key against a non unique parent: dbfiddle.uk/… The rule seems to be that if any of the referenced rows are deleted, a cascade delete is executed against the child
• nbk: mariadb and mysql work idetical, as long they are indexed you can reference columns, there is no need to be unique dev.mysql.com/doc/refman/8.0/en/…
• Lennart - Slava Ukraini: Thanks for the link. The behaviour is also described in the chapter on deviations from sql-standard: dev.mysql.com/doc/refman/8.0/en/ansi-diff-foreign-keys.html
• nbk: i like it, like some features in mysql, i wished they would add more features to their portfolio
• Lennart - Slava Ukraini: to me the behavior becomes counter-intuitive. In dbfiddle.uk/… the last insert/delete into PARENT changes the state of CHILD in a way that I would not expect.

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

• Lisa G: i thought so too actually, the direction leaves me appalled too, but i check my ER diagram, the instruction the teacher left us and an example and the direction seems to be correct. In the ER diagram DIPARTIMENTO is a multivalue attribute of the relationship prestito, so per instruction the direction should be that. That's a project for the final exam so I thought I was wrong and left it the way the teacher seems to want.
• mustaccio: I can't see you ER diagram from here, but your table definition obviously does not conform to it: there is no "DIPARTIMENTO is a multivalue attribute of the relationship prestito". And the fact that your logical design allows for multivalued attributes is in itself an error, not sure if on your part or your instructor.