1

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!

1
  • 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.)
    – J.D.
    May 29, 2021 at 13:47

2 Answers 2

0

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)
);
6
  • 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 May 29, 2021 at 12:32
  • 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/…
    – nbk
    May 29, 2021 at 12:52
  • 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 May 29, 2021 at 13:05
  • i like it, like some features in mysql, i wished they would add more features to their portfolio
    – nbk
    May 29, 2021 at 13:27
  • 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. May 30, 2021 at 8:38
1

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)
);
2
  • 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.
    – Lisa G
    May 30, 2021 at 9:54
  • 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.
    – mustaccio
    May 30, 2021 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.