[MySql] Concetto di chiave esterna

[D4rkAng3l]

Ho bisogno di una delucidazione...ditemi se quello che ho capito è corretto o se sbaglio qualcosa.

Facciamo conto che ho questa piccolissima base di dati (in realtà è parte di un esempio trovato su di un libro):

impiegato(IDimpiegato, nome, mansione, IDdipartimento)

dove: IDimpiegato è la CHIAVE ed IDdipartimento è la CHIAVE ESTERNA.

dipartimento(IDdipartimento, nome)

dove IDdipartimento è la CHIAVE.

Il relativo codice SQL dato dal libro dove ho tratto l'esempio è:

create table dipartimento
(
   IDdipartimento int not null auto_increment primary key,
   nome varchar(30)
) type=InnoDB;

create table impiegato
(
  IDimpiegato int not null auto_increment primary key,
  nome varchar(80),
  mansione varchar(30),
  IDdipartimento int not null references dipartimento(IDdipartimento)
) type=InnoDB;

La cosa che non mi torna troppo è il concetto di chiave esterna che usa questo libro.
L'altro libro usava il termine di chiave esterna per indicare un attributo di una tabella B che diventava parte della chiave di una tabella A per fare un esempio.
Ho una tabella STUDENTE ed una tabella UNIVERSITA', ogni riga della tabella STUDENTE sarà identificata dal campo matricola proprio di STUDENTE ma anche dalla chiave straniera nomeuniversità "importata" dalla tabella UNIVERSITA' in quanto altrimenti due studenti appartenenti ad università diverse potrebbero avere la stessa matricola.

Il libro dell'esempio precedente invece indica nella definizione delle tabelle il termine chiave esterna anche per indicare qualsiasi attributo pescato da un'altra tabella senza che questo alla fine non faccia efettivamente parte della chiave primaria della tabella in cui viene importato (come nell'esempio SQL da me riportato)...è giusto quello che ho capito o mi sfugge qualcosa?

Altra piccola cosa (sempre se non ho preso fischi per fiaschi prima)...per esempio io ho importato nella tabella impiegato l'attributo IDdipartimento dalla tabella dipartimento tramite l'istruzione:

IDdipartimento int not null references dipartimento(IDdipartimento)

ma questa cosa che significa esattamente? che nella tabella impiegato i valori del campo IDdipartimento devono essere coerenti con quelli del campo IDdipartimento della tabella dipartimento?

Per esempio non potrei inserire un valore in IDdipartimento di impiegato che non esiste già in IDdipartimento di dipartimento.

Ho capito bene? Vi prego di aiutarmi che lunedì prossimo ho l'esame e sono disperato.

Grazie
Andrea

[mad_hhatter]

Originariamente inviato da D4rkAng3l

...

una chiave esterna NON deve per forza far parte della chiave dello schema di relazione cui appartiene. si chiama chiave esterna perché identifica (è chiave) di una relazione esterna (cioè altra) rispetto a quella in cui compare

essendo chiave estrena i suoi valori possono essere soltanto
- null (se permesso)
- esistenti nella relazione riferita

IDdipartimento int not null references dipartimento(IDdipartimento)
significa letteralmente che è un attributo di tipo int, che non può essere null e che referenzia la relazione dipartimento rappresentandone la chiave primaria IDdipartimento

[D4rkAng3l]

ok...però per quanto riguarda il discorso che ho fatto sulla coerenza delle chiavi esterne come fatto nell'esempio va bene o non ho capito nulla?

Altra cosa...domanda probabilmente stupida che differenza c'è tra usare un'istruzione:

IDdipartimento int not null references dipartimento(IDdipartimento)

e usare una:

foreign key (deptno) references
dept(dptno) on update cascade on delete no action,

?!?!

Grazie
Andrea

[mad_hhatter]

Originariamente inviato da D4rkAng3l

ok...però per quanto riguarda il discorso che ho fatto sulla coerenza delle chiavi esterne come fatto nell'esempio va bene o non ho capito nulla?

Altra cosa...domanda probabilmente stupida che differenza c'è tra usare un'istruzione:

IDdipartimento int not null references dipartimento(IDdipartimento)

e usare una:

foreign key (deptno) references
dept(dptno) on update cascade on delete no action,

?!?!

Grazie
Andrea

ho modificato il primo post...

quanto all'ultima domanda: nessuna differenza (azioni a parte).
la seconda forma serve perché la chiave esterna potrebbe essere composta da più attributi che devono essere trattati come un'unica entità

[mad_hhatter]

[post doppio]

[D4rkAng3l]

Originariamente inviato da mad_hhatter

ho modificato il primo post...

quanto all'ultima domanda: nessuna differenza.
la seconda forma serve perché la chiave esterna potrebbe essere composta da più attributi che devono essere trattati come un'unica entità

Ah quindi se ho ben capito posso usare la seconda forma per importare tutta la chiave di una tabella B come attributi dentro una tabella A? che intendi che devono essere trattati come un'unica entità?

Grazie
Andrea

[mad_hhatter]

Originariamente inviato da D4rkAng3l

Ah quindi se ho ben capito posso usare la seconda forma per importare tutta la chiave di una tabella B come attributi dentro una tabella A? che intendi che devono essere trattati come un'unica entità?

Grazie
Andrea

supponi di avere lo schema seguente:

DIPARTIMENTO(ID, INDIRIZZO, attr1, attr2) avente chiave primaria (ID, INDIRIZZO)

supponi poi che alla relazione DIPARTIMENTO appartengano le tuple:
<0, blabla, null, null>
<1, albalb, 123, 456>

supponi poi di avere la relazione
IMPIEGATO(ID, nome, cognome, IDdip, IndDip)
in cui IDdip, IndDip identificano il dipartimento di afferenza.

ora, se IDdip e IndDip sono trattati come unica chiave esterna hai correttamente riferito una ben precisa tupla di DIPARTIEMNTO.

ma cosa succede se IDdip e IndDip sono trattati come 2 chiavi esterne indipendenti?

diventa lecito inserire in IMPIEGATO la tupla seguente:
<123, tizio, caio, 0, albalb>

come vedi, a livello teorico la tupla è lecita, ma rompe la coerenza della base di dati in quanto non esiste in DIPARTIEMNTO una tupla avente chiave (0, albalb)

[mad_hhatter]

POSTILLA: il corso di basi di dati l'ho fatto 2 anni fa... sto andando a memoria, quindi spero di non dire stronzate... mi raccomando: verifica le mie affermazioni con i tuoi testi...

comunque quella che la chiave esterna sia parte della chiave dello schema referente è una vaccata, su questo non ci sono dubbi



ATTENZIONE: ho appena dato un'occhiata veloce a wikipedia che mi ha fatto venire in mente questo: la dichiarazione
"IDdipartimento int not null references dipartimento(IDdipartimento)" potrebbe non essere del tutto corretta secondo lo standard sql... è probabile che la dichiarazione corretta sia "IDdipartimento int not null FOREIGN KEY references dipartimento(IDdipartimento)".

che poi alcuni dbms usino l'altra sistassi (ad esempio postgres) è un altro discorso... anche qui, per la sintassi esatta ti rimando a un testo affidabile

[D4rkAng3l]

Originariamente inviato da mad_hhatter

POSTILLA: il corso di basi di dati l'ho fatto 2 anni fa... sto andando a memoria, quindi spero di non dire stronzate... mi raccomando: verifica le mie affermazioni con i tuoi testi...

comunque quella che la chiave esterna sia parte della chiave dello schema referente è una vaccata, su questo non ci sono dubbi



ATTENZIONE: ho appena dato un'occhiata veloce a wikipedia che mi ha fatto venire in mente questo: la dichiarazione
"IDdipartimento int not null references dipartimento(IDdipartimento)" potrebbe non essere del tutto corretta secondo lo standard sql... è probabile che la dichiarazione corretta sia "IDdipartimento int not null FOREIGN KEY references dipartimento(IDdipartimento)".

che poi alcuni dbms usino l'altra sistassi (ad esempio postgres) è un altro discorso... anche qui, per la sintassi esatta ti rimando a un testo affidabile

booo io questa sintassi l'ho trovata su un libro chiamato MySql Tutorial edito da Pearson Education Italia.

La cosa della chiave esterna che fà parte della chiave primaria di un'altra tabella invece l'ho trovata sull'Atzeni della McGrowHill e invece credo sia giusta.

Immagina di avere due tabelle: STUDENTE ed UNIVERSITA'.

La prima rappresenta tutti gli studenti e la seconda le università e magari sono fatte così:

STUDENTE(matricola, nome, cognome, dataDiNascita, nomeUniversità)
UNIVERSITA'(nome, città, indirizzo)

Ora se la chiave di STUDENTE fosse solo la matricola si potrebbe avere l'indesiderabile situazione che:

0123, Mario, Rossi, 12/07/1982, Sapienza
0123, Luca Bianchi 21/01/1984, Tor Vergata

in pratica se è solo il campo matricola ad idenditifarmi le righe sono fregato perchè potrei avere due studenti di università diverse che hanno lo stesso numero di matricola.

Se invece faccio essere la chiave esterna nomeUniversità pescato dalla tabella UNIVERSITA' parte della chiave della tabella STUDENTE allora ogni riga di studente sarà identificata sia dalla matricola, sia dal nome dell'università e la situazione:
0123, Mario, Rossi, 12/07/1982, Sapienza
0123, Luca Bianchi 21/01/1984, Tor Vergata
è perfattamente risolta in quanto i due studenti vengono identificati rispettivamente da 0123 Sapienza e 0123 Tor Vergata. Di questo ne son praticamente certo :-)

Ciao
Andrea

[mad_hhatter]

Originariamente inviato da D4rkAng3l

booo io questa sintassi l'ho trovata su un libro chiamato MySql Tutorial edito da Pearson Education Italia.

La cosa della chiave esterna che fà parte della chiave primaria di un'altra tabella invece l'ho trovata sull'Atzeni della McGrowHill e invece credo sia giusta.

Immagina di avere due tabelle: STUDENTE ed UNIVERSITA'.

La prima rappresenta tutti gli studenti e la seconda le università e magari sono fatte così:

STUDENTE(matricola, nome, cognome, dataDiNascita, nomeUniversità)
UNIVERSITA'(nome, città, indirizzo)

Ora se la chiave di STUDENTE fosse solo la matricola si potrebbe avere l'indesiderabile situazione che:

0123, Mario, Rossi, 12/07/1982, Sapienza
0123, Luca Bianchi 21/01/1984, Tor Vergata

in pratica se è solo il campo matricola ad idenditifarmi le righe sono fregato perchè potrei avere due studenti di università diverse che hanno lo stesso numero di matricola.

Se invece faccio essere la chiave esterna nomeUniversità pescato dalla tabella UNIVERSITA' parte della chiave della tabella STUDENTE allora ogni riga di studente sarà identificata sia dalla matricola, sia dal nome dell'università e la situazione:
0123, Mario, Rossi, 12/07/1982, Sapienza
0123, Luca Bianchi 21/01/1984, Tor Vergata
è perfattamente risolta in quanto i due studenti vengono identificati rispettivamente da 0123 Sapienza e 0123 Tor Vergata. Di questo ne son praticamente certo :-)

Ciao
Andrea

attenzione!
l'atzeni l'ho usato e non credo abbia scritto una idiozia del genere... riporta il pezzo per favore, così vediamo di capirci...

quello postato da te è UN esempio particolare, ma la teoria non impone assolutamente che la chiave esterna faccia parte della chiave primaria della relazione referente (ovviamente deve invece costituire chiave per la relazione riferita). PUO' farne parte è ben diverso da DEVE farne parte.

quanto alla sitassi... se l'hai letta su un testo intitolato MySql blabla non è il testo migliore per cercare info sullo standard sql: mysql è semplicemente un dbms e in quanto tale utilizza UNA implementazione dello standard sql

[D4rkAng3l]

Originariamente inviato da mad_hhatter

attenzione!
l'atzeni l'ho usato e non credo abbia scritto una idiozia del genere... riporta il pezzo per favore, così vediamo di capirci...

quello postato da te è UN esempio particolare, ma la teoria non impone assolutamente che la chiave esterna faccia parte della chiave primaria della relazione referente (ovviamente deve invece costituire chiave per la relazione riferita). PUO' farne parte è ben diverso da DEVE farne parte.

quanto alla sitassi... se l'hai letta su un testo intitolato MySql blabla non è il testo migliore per cercare info sullo standard sql: mysql è semplicemente un dbms e in quanto tale utilizza UNA implementazione dello standard sql

sisi ma io intendevo che può farne parte e non che deve per forza farne parte (sull'Atzeni ci sono vari esempi anche di schemi E-R con l'archetto della chiave straniera)...la mia domanda iniziale era nata proprio dal fatto che l'atzeni e MySQL tutorial usano 2 notazioni diverse.

Nell'esempio che abbiamo visto prima (quello degli impiegati)l'Atzeni chiamerebbe "vincolo di integrità referenziale" quando prendi la chiave di una tabella B e la metti come attributo della tabella A per poter effettuare il join tra le due tabelle (e chiama identificatore esterno quando la chiave di B diventa parte della chiave di A come nell'esempio dell'università che avevo fatto) mentre il libro MySQL Tutorial chiama chiave esterna quando prendi la chiave di B e la metti come attributo di A e la rappresenta graficamente sottolineandola tratteggiata e sottolineando con una riga continua la chiave vera e propria (rappresentandola con una doppia sottolineatura se è anche chiave di A oltre ad essere chiave esterna).

Credo che il problema fosse in un uso diverso della terminologia tra i due libri perchè:

IDENTIFICATORE ESTERNO per Atzeni: è quando la chiave di B diventa parte della chiave di A perchè significa che tale attributo diventa parte dell'identificatore di A anche se è un campo di B e fà diversi sempi di questo (c'è anche un capitolo sul come tradurre una relazione di questo tipo da schema ER ristrutturato a tabelle).

VINCOLO DI INTEGRITA' REFERENZIALE per Atzeni: quando la chiave di B diventa un attributo semplice di A per poter effettuare il JOIN.

CHIAVE ESTERNA per MySql Tutorial: come il vincolo di integrità referenziale per Azterni.

CHIAVE ESTERNA CHE è ANCHE PRIMARY KEY per MySql Tutorial: come identificatore esterno per Azteni...

Avevo fatto un po' di casino soprattutto impicciandomi con i termini CHIAVE ESTERNA ed IDENTIFICATORE ESTERNO che sono simili ma di fatto i due libri li usano in maniera un po' diversa

[mad_hhatter]

Originariamente inviato da D4rkAng3l

sisi ma io intendevo che può farne parte e non che deve per forza farne parte (sull'Atzeni ci sono vari esempi anche di schemi E-R con l'archetto della chiave straniera)...la mia domanda iniziale era nata proprio dal fatto che l'atzeni e MySQL tutorial usano 2 notazioni diverse.

Nell'esempio che abbiamo visto prima (quello degli impiegati)l'Atzeni chiamerebbe "vincolo di integrità referenziale" quando prendi la chiave di una tabella B e la metti come attributo della tabella A per poter effettuare il join tra le due tabelle (e chiama identificatore esterno quando la chiave di B diventa parte della chiave di A come nell'esempio dell'università che avevo fatto) mentre il libro MySQL Tutorial chiama chiave esterna quando prendi la chiave di B e la metti come attributo di A e la rappresenta graficamente sottolineandola tratteggiata e sottolineando con una riga continua la chiave vera e propria (rappresentandola con una doppia sottolineatura se è anche chiave di A oltre ad essere chiave esterna).

Credo che il problema fosse in un uso diverso della terminologia tra i due libri perchè:

IDENTIFICATORE ESTERNO per Atzeni: è quando la chiave di B diventa parte della chiave di A perchè significa che tale attributo diventa parte dell'identificatore di A anche se è un campo di B e fà diversi sempi di questo (c'è anche un capitolo sul come tradurre una relazione di questo tipo da schema ER ristrutturato a tabelle).

VINCOLO DI INTEGRITA' REFERENZIALE per Atzeni: quando la chiave di B diventa un attributo semplice di A per poter effettuare il JOIN.

CHIAVE ESTERNA per MySql Tutorial: come il vincolo di integrità referenziale per Azterni.

CHIAVE ESTERNA CHE è ANCHE PRIMARY KEY per MySql Tutorial: come identificatore esterno per Azteni...

Avevo fatto un po' di casino soprattutto impicciandomi con i termini CHIAVE ESTERNA ed IDENTIFICATORE ESTERNO che sono simili ma di fatto i due libri li usano in maniera un po' diversa

la terminologia dell'atzeni è decisamente più rigorosa e precisa.
se il tutorial di mysql chiama chiave esterna un attributo che appartiene alla chiave, usa una terminologia a dir poco ambigua. ti consiglio di lasciar perdere quel tutorial e concentrarti sull'atzeni: era anche il mio libro di testo

in ogni caso:
- si dice chiave esterna, non straniera
- la dicitura "chiave esterna" non ha nulla a che fare con il concetto di "chiave" di uno schema di relazione: una chiave esterna è semplicemente un vincolo di integrità referenziale tra due schemi di relazione. che poi la chiave esterna appartenga alla (e, in particolare, costutuisca la) chiave di uno schema di relazione è un discorso diverso che non ha a che fare con la definizione di chiave esterna

[D4rkAng3l]

Originariamente inviato da mad_hhatter

la terminologia dell'atzeni è decisamente più rigorosa e precisa.
se il tutorial di mysql chiama chiave esterna un attributo che appartiene alla chiave, usa una terminologia a dir poco ambigua. ti consiglio di lasciar perdere quel tutorial e concentrarti sull'atzeni: era anche il mio libro di testo

in ogni caso:
- si dice chiave esterna, non straniera
- la dicitura "chiave esterna" non ha nulla a che fare con il concetto di "chiave" di uno schema di relazione: una chiave esterna è semplicemente un vincolo di integrità referenziale tra due schemi di relazione. che poi la chiave esterna appartenga alla (e, in particolare, costutuisca la) chiave di uno schema di relazione è un discorso diverso che non ha a che fare con la definizione di chiave esterna

perfetto...ora ho le idee più chiare...mah il mysql tutorial è un librettino piccinino...non tratta la progettazione di db...più che altro è utile per i comandi base di mysql, come fare le query...per quello è abbastanza carino anche se per il resto non va bene

[mad_hhatter]

Originariamente inviato da D4rkAng3l

perfetto...ora ho le idee più chiare...mah il mysql tutorial è un librettino piccinino...non tratta la progettazione di db...più che altro è utile per i comandi base di mysql, come fare le query...per quello è abbastanza carino anche se per il resto non va bene

capisco, ma se stai preparando un esame probabilmente avrai in programma lo standard sql, non una sua implementazione specifica...

quindi occhio ai dialetti

e soprattutto, per la terminologia, le definizioni e la teoria in genere usa l'atzeni!

[D4rkAng3l]

Originariamente inviato da mad_hhatter

capisco, ma se stai preparando un esame probabilmente avrai in programma lo standard sql, non una sua implementazione specifica...

quindi occhio ai dialetti

e soprattutto, per la terminologia, le definizioni e la teoria in genere usa l'atzeni!

sisi ma la toeria l'ho studiata tutta sull'Atzeni anche perchè sull'altro non c'è...mi ero messo a fare un esempio del librettino usando i comandini create database...create table e mi ero incasinato in questa divergenza :-) cmq da ora in poi terrò come verbo solo ciò che dice l'Atzeni

[mad_hhatter]

Originariamente inviato da D4rkAng3l

sisi ma la toeria l'ho studiata tutta sull'Atzeni anche perchè sull'altro non c'è...mi ero messo a fare un esempio del librettino usando i comandini create database...create table e mi ero incasinato in questa divergenza :-) cmq da ora in poi terrò come verbo solo ciò che dice l'Atzeni

a scanso di equivoci: anche l'atzeni usa una terminologia SUA propria... altri testi teorici possono usare, e spesso usano, terminologie leggermente differenti... te lo dico nel caso ti capitasse in mano un testo teorico diverso e cominciassi a notare differenze...

comunque sto divagando

in bocca al lupo per l'esame e se hai altri dubbi non esitare a chiedere

[D4rkAng3l]

crepi il lupo...ti ringrazio molto...mi hai chiarito un bel po' le idee