References e Foreign Key in SQL
References crea un legame tra i valori di un attributo di una tabella slave e quelli di un attributo di una tabella master (esterna). Se il vincolo coinvolge più attributi si usa Foreign Key.
Si tratta di un vincolo di integrità interrelazionale perché coinvolge due tabelle.
E' anche detto vincolo di chiave esterna (foreign key) perché limita i valori di un attributo al range dei valori presenti in una tabella esterna.
Nota. L'attributo nella tabella esterna non deve essere duplicato. Pertanto, deve essere associato a un vincolo di Primary Key oppure Unique.
La tabella slave è anche detta tabella referente, tabella interna o tabella locale perché il vincolo di chiave esterna va indicato tra i vincoli dell'attributo slave.
References
La parola References va indicata nella tabella interna (slave) nei vincoli dell'attributo coinvolto, seguito dal nome della tabella esterna (master) e dell'attributo tra parentesi tonde.
references tabella_esterna(attributo)
Si usa il vincolo References soltanto quando il vincolo di chiave esterna coinvolge un solo attributo.
Un esempio pratico
Ho una tabella Clienti con l'anagrafica dei clienti di un'azienda.
L'attributo CodiceCliente è la chiave primaria (primary key) della tabella.
Devo creare una tabella Ordini facendo in modo che ogni ordine sia associato a un cliente registrato in anagrafica.
Per realizzare questo obiettivo creo la tabella Ordini associando un vincolo di chiave esterna all'attributo Cliente con l'attributo CodiceCliente della tabella Clienti.
CREATE TABLE Ordini
(
Cliente Number(5) References Clienti(CodiceCliente),
NumOrdine Number(9),
Data Date(20),
Importo Float(12)
)
In questo vincolo interrelazionale la tabella Ordini è slave (tabella interna) mentre la tabella Clienti è master (tabella esterna).
Pertanto, l'inserimento o la modifica di un dato nella tabella Ordini è subordinato all'esistenza del valore Cliente nel campo CodiceCliente della tabella Ordini.
In caso contrario si verifica una violazione di integrità della chiave esterna.
Foreign Key
La parola Foreign Key va indicata nella tabella interna (slave) dopo la definizione degli attributi per indicare gli attributi interni ed esterni coinvolti nel vincolo di chiave esterna.
Foreign Key (attributi interni) references tabella_esterna (attributi esterni)
Si usa il vincolo Foreign Key quando il vincolo di chiave esterna coinvolge più attributi.
Nella lista gli attributi devono essere separati tra loro da una virgola.
Nota. La corrispondenza degli attributi interni e quelli esterni segue l'ordine di presentazione nella lista.
Un esempio pratico
Ho una tabella Clienti (master).
I campi Nome e Cognome sono definiti unici tramite il vincolo interno Unique.
Creo una tabella Ordini (slave) facendo in modo che il Cognome e il Nome siano associati al vincolo di chiave esterna con gli attributi Cognome e Nome della tabella Clienti (master).
CREATE TABLE Ordini
(
Cognome Varchar(20),
Nome Varchar(20),
NumOrdine Number(9),
Data Date(20),
Importo Float(12),
Foreign Key (Cognome, Nome) references Clienti(Cognome, Nome)
)
In questo caso i valori nel campo Cognome e Nome nella tabella Ordini devono essere presenti in una riga della tabella Clienti (master).
Se inserisco una nuova riga nella tabella Ordini indicando un cognome e nome non presente nella tabella Clienti si verifica una violazione di integrità della chiave esterna.
Nota. Si verifica una violazione di integrità anche se cerco di modificare il cognome e nome di una riga già presente nella tabella Ordini usando dei valori non presenti nella tabella Clienti. Ad esempio, se modifico il cognome da Bianchi a Rossi nella seconda riga della tabella Ordini si verifica una violazione della chiave esterna perché non esiste una corrispondenza "Giuseppe Rossi" nella tabella Clienti.
La gestione delle violazioni
Le violazioni di integrità della chiave esterna determinano un comportamento del DBMS che, almeno in parte, può essere personalizzato a seconda dell'origine della violazione.
Caso 1 : origine nella tabella slave
Se la causa della violazione si verifica nella tabella slave a seguito dell'inserimento di nuovi dati nella tabella (INSERT INTO) o della modifica dei dati già esistenti (UPDATE), il DBMS rifiuta l'operazione.
Pertanto, non è possibile inserire un nuovo dato, né modificare un dato esistente nella tabella slave, se non rispetta il vincolo di chiave esterna.
In questo caso il comportamento del DBMS non può essere personalizzato.
Caso 2 : origine nella tabella master
Se la causa della violazione si verifica nella tabella slave a seguito della modifica (UPDATE) o della cancellazione (DELETE) di una chiave esterna, posso invece personalizzare la risposta del DBMS.
La gestione della violazione va indicata nella tabella slave dopo il vincolo di integrità REFERENCES o FOREIGN KEY.
ON [DELETE|UPDATE] [CASCADE|SET NULL|SET DEFAULT|NO ACTION]
Il primo parametro definisce l'evento causa (DELETE o UPDATE) mentre il secondo parametro definisce la risposta del DBMS (detta politica)
- CASCADE
Una modifica della chiave esterna nella tabella master, modifica a cascata anche i valori referenti nella tabella slave con il nuovo valore della chiave. La cancellazione della chiave esterna causa la cancellazione delle righe referenti nella tabella slave. - SET NULL
Una modifica o la cancellazione della chiave esterna nella tabella master, modifica automaticamente i relativi valori della chiave nella tabella slave con un valore NULL. - SET DEFAULT
Una modifica o la cancellazione della chiave esterna nella tabella master, modifica automaticamente i relativi valori della chiave nella tabella slave con il valore di default dell'attributo. - NO ACTION
L'azione di modifica viene bloccata.
Un esempio pratico
Creo la tabella referente Ordini con un vincolo di chiave esterna sull'attributo Cliente.
CREATE TABLE Ordini
(
Cliente Number(5)
References Clienti(CodiceCliente)
On Update Cascade
On Delete Set Null,
NumOrdine Number(9),
Data Date(20),
Importo Float(12)
)
La modifica della chiave esterna (ON UPDATE) nella tabella Clienti aggiorna automaticamente i valori referenti nella tabella Ordini tramite l'opzione CASCADE.
La cancellazione della chiave esterna (ON DELETE) nella tabella Clienti, invece, sostituisce i valori referenti nella tabella Ordini con un valore nullo (SET NULL).
Esempio 2
Creo una tabella usando un vincolo di chiave tramite Foreign Key
CREATE TABLE Ordini
(
Cognome Varchar(20),
Nome Varchar(20),
NumOrdine Number(9),
Data Date(20),
Importo Float(12),
Foreign Key (Cognome, Nome) references Clienti(Cognome, Nome)
On Update Cascade
On Delete Set Null
)
La modifica delle chiavi esterne (ON UPDATE) è gestita con la politica CASCADE.
La cancellazione delle chiavi esterne (ON DELETE) è invece gestita con la politica SET NULL.
E così via.
