5. Relazioni tra tabelle

5.1. Chiavi esterne

Un database relazionale è un insieme di tabelle collegate tra loro da relazioni. Prendiamo un esempio ispirato alla precedente tabella [BIBLIO], che aveva la seguente struttura:

Un esempio del contenuto era il seguente:

Potresti voler ottenere informazioni sui vari autori di queste opere, come il loro nome e cognome, la data di nascita e la nazionalità. Creiamo una tabella di questo tipo. Fai clic con il tasto destro del mouse su [DBBIBLIO / Tabelle] e seleziona l'opzione [Nuova tabella]:

Ora creiamo la seguente tabella [AUTHORS]:

`id`	chiave primaria della tabella — utilizzata per identificare in modo univoco una riga
`cognome`	cognome dell'autore
`nome`	nome dell'autore, se applicabile
`data_di_nascita`	data di nascita
`cittadinanza`	paese di origine

Il contenuto della tabella [AUTHORS] potrebbe essere il seguente:

Torniamo alla tabella [BIBLIO] e al suo contenuto:

Nella colonna [AUTHOR] della tabella non è più necessario inserire il nome dell'autore. È invece preferibile inserire il numero ID assegnatogli nella tabella [AUTHORS]. Creiamo una nuova tabella chiamata [BOOKS]. Per crearla, useremo lo script [biblio.sql] creato nella sezione 3.14. Eseguiamo questo script utilizzando lo strumento [Script Executive, Ctrl-F12]:

Modifichiamo lo script per la creazione della tabella BIBLIO per adattarlo alla tabella BOOKS:

CREATE TABLE LIVRES (
ID          INTEGER NOT NULL,
TITRE       VARCHAR(30) NOT NULL,
AUTEUR      INTEGER,
GENRE       VARCHAR(30) NOT NULL,
ACHAT       DATE NOT NULL,
PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
DISPONIBLE  CHAR(1) NOT NULL
);

INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (2, 'Les fleurs du mal', 8, 'POèME', '1978-01-01', 120, 'n');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (3, 'Tintin au Tibet', 6, 'BD', '1990-11-10', 70, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (4, 'Du côté de chez Swann', 3, 'ROMAN', '1978-12-08', 220.5, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (5, 'La terre', 4, 'ROMAN', '1990-06-12', 55.13, 'n');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (6, 'Madame Bovary', 1, 'ROMAN', '1988-03-12', 143.33, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (7, 'Manhattan transfer', 7, 'ROMAN', '1987-08-30', 352.8, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (8, 'Tintin en Amérique', 6, 'BD', '1991-05-15', 70, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (10, 'Le père Goriot', 5, 'Roman', '1991-09-01', 210, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (11, 'Poèmes saturniens', 2, 'Poème', '1992-09-02', 200, 'o');

COMMIT WORK;



/******************************************************************************/
/****                          Unique Constraints                          ****/
/******************************************************************************/

ALTER TABLE LIVRES ADD CONSTRAINT UNQ1_LIVRES UNIQUE (TITRE);


/******************************************************************************/
/****                             Primary Keys                             ****/
/******************************************************************************/

ALTER TABLE LIVRES ADD CONSTRAINT PK_LIVRES PRIMARY KEY (ID);

Commenteremo solo le modifiche:

riga 4: la colonna [AUTHOR] nella tabella diventa un numero intero. Questo numero fa riferimento a uno degli autori nella tabella [AUTHORS] creata in precedenza.
Righe 11–19: i nomi degli autori sono stati sostituiti dai loro ID autore.
riga 29: il nome del vincolo è stato modificato. In precedenza si chiamava [UNQ1_BIBLIO]. Ora si chiama [UNQ1_LIVRES]. Questo nome può essere qualsiasi cosa. Tuttavia, è preferibile che sia significativo. In questo caso, non è stato fatto questo sforzo. I vincoli su campi e tabelle diversi all'interno di un database devono essere distinti da nomi diversi. Si ricordi che il vincolo alla riga 29 richiede che un titolo sia univoco all'interno della tabella.
Riga 36: Modificare il nome del vincolo sulla chiave primaria ID.

Eseguiamo questo script. Se l'operazione va a buon fine, otteniamo la seguente nuova tabella [BOOKS]:

Ci si potrebbe chiedere se alla fine abbiamo effettivamente ottenuto qualcosa. In effetti, la tabella [BOOKS] elenca i numeri degli autori invece dei loro nomi. Dato che ci sono migliaia di autori, collegare un libro al suo autore sembra difficile. Fortunatamente, SQL è qui per aiutarci. Ci permette di interrogare più tabelle contemporaneamente. Per questo esempio, presentiamo la query SQL che ci permette di recuperare i titoli dei libri presenti in biblioteca, insieme alle informazioni sui loro autori. Utilizziamo l'editor SQL (F12) per eseguire la seguente istruzione SQL:

SQL> select LIVRES.titre, AUTEURS.nom, AUTEURS.prenom,AUTEURS.date_naissance
FROM LIVRES inner join AUTEURS on LIVRES.AUTEUR=AUTEURS.ID
ORDER BY AUTEURS.nom asc

È troppo presto per spiegare questa query SQL. Ci torneremo tra poco. Il risultato di questa query è il seguente:

Ogni libro è stato correttamente abbinato al proprio autore e alle informazioni associate.

Riassumiamo ciò che abbiamo appena fatto:

abbiamo due tabelle contenenti diversi tipi di informazioni:
- la tabella AUTHORS contiene informazioni sugli autori
- la tabella BOOKS contiene informazioni sui libri acquistati dalla biblioteca
queste tabelle sono collegate tra loro. Un libro deve avere un autore. Può anche averne diversi. Questo scenario non è stato preso in considerazione in questa sede. Il campo [AUTHOR] nella tabella [BOOKS] fa riferimento a una riga nella tabella [AUTHORS]. Questo si chiama relazione.

La relazione tra la tabella [BOOKS] e la tabella [AUTHORS] è in realtà un tipo di vincolo: una riga nella tabella [BOOKS] deve sempre avere un ID autore che esiste nella tabella [AUTHORS]. Se una riga in [BOOKS] avesse un ID autore che non esiste nella tabella [AUTHORS], ci troveremmo in una situazione anomala in cui non saremmo in grado di trovare l'autore di un libro.

Il DBMS è in grado di garantire che questo vincolo sia sempre soddisfatto. Per farlo, aggiungeremo un vincolo alla tabella [BOOKS]:

Il collegamento che unisce la colonna [AUTHOR] della tabella [BOOKS] al campo [ID] della tabella [AUTHORS] è chiamato relazione di chiave esterna. Il campo [AUTHOR] della tabella [BOOKS] è indicato come "chiave esterna" nella procedura guidata sopra riportata. Definire una chiave esterna significa che il valore di una colonna [c1] in una tabella [T1] deve esistere nella colonna [c2] della tabella [T2]. La colonna [c1] è indicata come la "chiave esterna" della tabella T1 sulla colonna [c2] della tabella [T2]. La colonna [c2] è spesso la chiave primaria della tabella [T2], ma ciò non è obbligatorio.

Definiamo la chiave esterna [AUTHOR] della tabella [BOOKS] sul campo [ID] della tabella [AUTHORS] come segue:

nome del vincolo: free
colonna "chiave esterna", in questo caso la colonna [AUTHOR] della tabella [BOOKS]
tabella a cui fa riferimento la chiave esterna. In questo caso, la colonna [AUTHOR] della tabella [BOOKS] deve avere un valore nella colonna [ID] della tabella [AUTHORS]. Pertanto, la tabella [AUTHORS] è la tabella di riferimento.
Colonna a cui fa riferimento la chiave esterna. In questo caso, la colonna [ID] della tabella [AUTHORS].

Convalidiamo questo vincolo:

Se tutto va bene, viene accettato:

Qual è la conseguenza di questo nuovo vincolo di chiave esterna? Utilizzando l'editor SQL (F12), proviamo a inserire una riga nella tabella BOOKS con un ID autore inesistente:

L'operazione [INSERT] sopra riportata ha tentato di inserire un libro con un ID autore inesistente (100). L'esecuzione della query non è andata a buon fine. Il messaggio di errore associato indica che si è verificata una violazione del vincolo di chiave esterna "FK_BOOKS_AUTHORS". Si tratta di quello che abbiamo appena definito.

5.2. Operazioni di join tra due tabelle

Sempre nel database [DBBIBLIO] (o in qualsiasi altro database), creiamo due tabelle di prova denominate TA e TB e definiamole come segue:

Tabella TA

- ID: chiave primaria della tabella TA

- DATA: dati arbitrari

Tabella TB

- ID: chiave primaria della tabella TB

- IDTA: chiave esterna della tabella TB che fa riferimento alla colonna ID della tabella TA. Pertanto, un valore della colonna IDTA della tabella TA deve esistere nella colonna ID della tabella TA

- VALUE: qualsiasi dato

Nell'editor SQL (F12), eseguiremo istruzioni SQL che utilizzano contemporaneamente entrambe le tabelle TA e TB.

SQL>select * from TA,  TB

L'istruzione SQL utilizza la parola chiave FROM per fare riferimento alle tabelle TA e TB. L'operazione FROM TA, TB determinerà la creazione di una nuova tabella temporanea, in cui ogni riga della tabella TA verrà unita a ogni riga della tabella TB. Pertanto, se la tabella TA ha NA righe e la tabella TB ha NB righe, la tabella risultante avrà NA x NB righe. Ciò è mostrato nella schermata sopra. Inoltre, ogni riga contiene le colonne di entrambe le tabelle. Le colonne specificate nell'ordine [SELECT col1, col2, ... FROM ...] indicano quali includere. In questo caso, la parola chiave * indica che vengono richieste tutte le colonne della tabella risultante. La tabella risultante dall'istruzione SQL precedente viene talvolta definita come il prodotto cartesiano delle tabelle TA e TB.

Sopra, ogni riga della tabella TA è stata associata a ogni riga della tabella TB. In generale, vogliamo associare una riga di TB a una riga di TA che abbia una relazione con essa. Questa relazione spesso assume la forma di un vincolo di chiave esterna. Questo è il caso qui. A una riga nella tabella TA, possiamo associare le righe nella tabella TB che soddisfano la relazione TB.IDTA=TA.ID. Ci sono diversi modi per eseguire questa query:

SQL>select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA, TB where TA.ID=TB.IDTA

L'istruzione SQL precedente è simile a quella precedente, con due differenze:

le righe risultanti dal prodotto cartesiano TA x TB vengono filtrate da una clausola WHERE che associa a una riga della tabella TA solo quelle righe della tabella TB che soddisfano la relazione TB.IDTA=TA.ID
vengono richieste solo determinate colonne utilizzando la sintassi [T.col], dove T è il nome di una tabella e col è il nome di una colonna in quella tabella. Questa sintassi risolve qualsiasi ambiguità che potrebbe sorgere se due tabelle avessero colonne con lo stesso nome. Quando questa ambiguità non esiste, è possibile utilizzare la sintassi [col] senza specificare la tabella per quella colonna.

Il risultato è il seguente:

Lo stesso risultato può essere ottenuto con la seguente istruzione SQL:

SQL>select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA inner join TB on TA.ID=TB.idta

Il termine [inner join] dà origine al nome "inner join" attribuito a questo tipo di operazione tra due tabelle. Vedremo che esiste anche un "outer join". In un inner join, l'ordine delle tabelle nella query non ha alcun effetto sul risultato: FROM TA inner join TB è equivalente a FROM TB inner join TA.

L'ordine SQL precedente include nel set di risultati solo quelle righe della tabella TA a cui fa riferimento almeno una riga della tabella TB. Pertanto, la riga [3, data3] in TA non compare nel risultato perché non è referenziata da una riga in TB. Potresti volere tutte le righe di TA, indipendentemente dal fatto che siano referenziate o meno da una riga in TB. In tal caso, si utilizza un left outer join tra le due tabelle:

SQL> select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA left outer join TB on TA.ID= TB.IDTA

Qui abbiamo un join esterno sinistro. Per comprendere il termine "FROM TA left outer join TB", immaginate un join con la tabella TA a sinistra e la tabella TB a destra. Tutte le righe della tabella a sinistra compaiono nel risultato di un join esterno sinistro, anche quelle per le quali la condizione di join non è soddisfatta. Questa condizione di join non è necessariamente un vincolo di chiave esterna, sebbene questo sia il caso più comune.

Nel seguente ordine:

SQL> select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TB left outer join TA on TA.ID= TB.IDTA

la tabella TB si trova sul lato "sinistro" dell'outer join. Pertanto, tutte le righe di TB appariranno nel risultato:

A differenza di un inner join, in questo caso l'ordine delle tabelle è importante. Esistono anche i right outer join:

FROM TA LEFT OUTER JOIN TB è equivalente a FROM TB RIGHT OUTER JOIN TA: la tabella TA si trova a sinistra
FROM TB LEFT OUTER JOIN TA è equivalente a FROM TA RIGHT OUTER JOIN TB: la tabella TB si trova a sinistra

Ora che abbiamo compreso le basi dell'interrogazione simultanea di più tabelle, possiamo passare a query di database più complesse.