Qual è il vantaggio delle chiavi esterne in SQL?

Che cos'è una chiave esterna e perché è così importante nei database relazionali? Scoprite tutti i dettagli sulle chiavi esterne in questo articolo.

Una delle caratteristiche principali dei database relazionali è la possibilità di collegare i dati memorizzati in tabelle diverse. Questi collegamenti, chiamati riferimenti, agiscono essenzialmente come connessioni tra le tabelle. Vengono creati utilizzando il vincolo FOREIGN KEY sulle colonne delle tabelle.

In questo articolo scopriremo cosa fa il vincolo FOREIGN KEY in SQL. Verrà spiegato come definirlo con l'istruzione CREATE TABLE e verranno illustrati alcuni esempi. Poi parleremo dei suoi vantaggi e delle sue caratteristiche. In particolare:

• Impareremo a conoscere la relazione tra la tabella primaria (che fornisce i valori delle sue colonne di chiave primaria alla tabella esterna) e la tabella esterna (che utilizza le colonne fornite dalla tabella primaria come chiave esterna).
• Parleremo di cosa succede quando i valori delle colonne della tabella primaria vengono cancellati o modificati. In queste situazioni, il vincolo FOREIGN KEY offre diverse opzioni, che verranno chiarite con alcuni esempi.
• Infine, discuteremo le opzioni di cardinalità che possono essere implementate con il vincolo . FOREIGN KEY

Iniziamo.

Cos'è un vincolo di chiave esterna in SQL?

Per comprendere il concetto di vincolo FOREIGN KEY in SQL, si può pensare a un collegamento di riferimento tra tabelle note come primarie (o genitore) e tabelle estranee (o figlio). La tabella esterna fa riferimento a una o più colonne (la chiave primaria, che può essere una o più colonne) della tabella primaria; è così che si crea il collegamento. Se avete bisogno di rinfrescare le vostre conoscenze sulle chiavi primarie in SQL, vi consiglio di leggere l'articolo Cos'è una chiave primaria?

Un esempio in immagini

Credo che alcuni supporti visivi possano essere utili in questo caso. Guardiamo le immagini qui sotto.

Qui abbiamo la tabella Airplane (la tabella primaria) e la tabella Flight (la tabella esterna). La colonna AirplaneId, che è una colonna chiave primaria per la tabella Airplane, è utilizzata come colonna chiave esterna nella tabella . Flight tabella. In questo modo viene creata la relazione tra queste tabelle: la colonna AirplaneId della tabella definisce l'aereo utilizzato. Flight definisce quale aereo viene utilizzato per ogni volo.

Si noti che la colonna AirplaneId della tabella straniera Flight non deve necessariamente contenere tutti i valori memorizzati in Airplane.AirplaneId. Può contenere un sottoinsieme di questi valori:

I valori contenuti nella colonna Flight.AirplaneId si riferiscono direttamente ai valori contenuti nella colonna Airplane.AirplaneId. Di conseguenza, qualsiasi azione di aggiornamento o cancellazione delle righe della tabella primaria Airplane deve essere riflessa di conseguenza dalla tabella esterna Flight. Il vincolo FOREIGN KEY offre diverse opzioni per implementare questi aggiornamenti o cancellazioni, che verranno discusse in seguito.

Un'altra cosa importante. L'esempio precedente mostra una relazione tra le tabelle Airplane e Flight utilizzando una sola colonna. È anche possibile utilizzare più colonne per creare tali relazioni tra tabelle.

In questo caso, invece di usare la colonna AirplaneId per il vincolo FOREIGN KEY, abbiamo deciso di usare le colonne AirplaneBrand e AirplaneModel, poiché anch'esse identificano in modo univoco ciascuna riga della tabella Airplane (supponendo che la nostra compagnia aerea possieda un solo aereo per ogni coppia marca-modello elencata nella tabella).

Si può notare che potremmo anche inserire tutti i dati in una tabella chiamata AirplaneFlight. È vero, ma in questo caso non saremmo in grado di suddividere i dati in diverse categorie all'interno di una tabella. Questo è il motivo per cui le chiavi esterne sono una parte fondamentale della progettazione di un database. Esse consentono di inserire dati correlati in più tabelle e di collegarle tra loro per mantenerne l'integrità.

Come definire il vincolo di chiave esterna in SQL

Ora vogliamo portare il nostro progetto nel database. Possiamo farlo utilizzando l'istruzione CREATE TABLE e definendo il vincolo FOREIGN KEY al suo interno.

Vediamo come appare l'istruzione CREATE TABLE se definiamo il vincolo FOREIGN KEY sulla base dell'esempio precedente, che utilizza la colonna AirplaneId.

Per prima cosa, è necessario creare la tabella primaria e definire la sua colonna chiave primaria.

CREATE TABLE Airplane (
AirplaneId VARCHAR(10) NOT NULL,
AirplaneBrand VARCHAR(30) NOT NULL,
AirplaneModel VARCHAR(30) NOT NULL,
CONSTRAINT PK_AirplaneId PRIMARY KEY (AirplaneId)
);

Per esaminare il vincolo PRIMARY KEY, vedere Che cos'è una chiave primaria in SQL?

Poi si crea la tabella esterna:

CREATE TABLE Flight (
FlightId VARCHAR(10) NOT NULL,
AirplaneId VARCHAR(10) NOT NULL,
PilotId INTEGER NOT NULL,
CONSTRAINT PK_FlightId PRIMARY KEY (FlightId),
CONSTRAINT FK_AirplaneId FOREIGN KEY (AirplaneId)
REFERENCES Airplane(AirplaneId)
);

Qui, oltre al vincolo PRIMARY KEY, abbiamo definito il vincolo FOREIGN KEY. Gli abbiamo dato il nome FK_AirplaneId. Tra parentesi, abbiamo definito la colonna della tabella Flight che implementa il vincolo FOREIGN KEY. Dopo la parola chiave REFERENCES, seguono il nome della tabella primaria e una colonna.

In questo modo possiamo trasformare il nostro progetto di database in un database funzionante.

Può accadere che si voglia aggiungere il vincolo FOREIGN KEY alla tabella dopo che questa è stata creata. Flight alla tabella dopo che questa è stata creata. Per farlo, si utilizza l'istruzione ALTER TABLE:

ALTER TABLE Flight ADD FOREIGN KEY FK_AirplaneId (AirplaneId)
REFERENCES Airplane(AirplaneId);

Potremmo anche voler eliminare un vincolo FOREIGN KEY. Supponiamo di voler cambiare il vincolo FOREIGN KEY definito in precedenza con la sua versione a più colonne. Dobbiamo prima eliminarlo e poi creare la nuova chiave:

ALTER TABLE Flight DROP FOREIGN KEY FK_AirplaneId;
ALTER TABLE Flight ADD FOREIGN KEY FK_Airplane (AirplaneBrand, AirplaneModel)
REFERENCES Airplane(AirplaneBrand, AirplaneModel);

Ora la tabella<strong>Flight utilizza il vincolo multicolonna FOREIGN KEY .

Se ritenete di dover fare più pratica con la creazione di tabelle di database, date un'occhiata al nostro corso su Le basi della creazione di tabelle in SQL.

Quali sono i vantaggi delle chiavi esterne?

Ora che abbiamo appreso tutte le basi del vincolo FOREIGN KEY, le sue caratteristiche e le possibilità che offre per la funzionalità del database, possiamo approfondire i suoi vantaggi.

Come ho già detto, quando si implementa un vincolo FOREIGN KEY, si può distinguere tra tabella primaria e tabella esterna.

La tabella primaria fornisce una colonna, o un insieme di colonne, che viene utilizzata dalla tabella esterna. In altre parole, la colonna (o l'insieme di colonne) della tabella esterna fa riferimento a una colonna (o a un insieme di colonne) della tabella primaria.

Quindi, il primo vantaggio del vincolo FOREIGN KEY è che garantisce l'esistenza della riga di riferimento nella tabella primaria. Se la riga di riferimento non è presente nella tabella primaria, non può essere presente nella tabella esterna.

Un altro vantaggio del vincolo FOREIGN KEY (FK) è che garantisce la correttezza del riferimento anche quando i valori della tabella primaria vengono modificati o eliminati. Esiste una serie di azioni che indicano cosa fare in caso di aggiornamento o cancellazione di una riga di chiave primaria (PK). Vediamo quali sono queste azioni.

Quando una riga di chiave primaria viene eliminata

Il vincolo FOREIGN KEY offre una serie di opzioni per quanto riguarda il comportamento da adottare in caso di cancellazione di righe di chiave primaria dalla tabella primaria.

Per impostazione predefinita, quando non viene specificata alcuna azione per il vincolo FOREIGN KEY, non è possibile eliminare la riga in questione nella tabella primaria (genitore) se ci sono righe che fanno riferimento ad essa nella tabella esterna (figlio).

È possibile specificare l'azione da intraprendere per l'eliminazione delle righe PK della tabella primaria scegliendo una delle seguenti opzioni:

• ON DELETE CASCADE
• ON DELETE RESTRICT
• ON DELETE NO ACTION
• ON DELETE SET NULL
• ON DELETE SET DEFAULT

Esaminiamo ciascuna di esse. Per prima cosa, esamineremo la sintassi che accomuna tutte queste opzioni.

Sintassi di ON DELETE

La dichiarazione dell'opzione ON DELETE segue direttamente la dichiarazione del vincolo FOREIGN KEY.

ALTER TABLE Flight ADD FOREIGN KEY FK_AirplaneId (AirplaneId)
REFERENCES Airplane(AirplaneId)
ON DELETE ;

Ora analizziamo tutte le opzioni.

ON DELETE A CASCATA

L'opzione ON DELETE CASCADE assicura che quando una riga viene rimossa dalla PK della tabella primaria, venga rimossa anche la riga della tabella esterna che la referenzia.

Vediamola in azione utilizzando i nostri Airplane e Flight e le nostre tabelle. Per prima cosa, abbiamo le due tabelle correlate:

Rimuoviamo una riga dalla tabella primaria:

E ora, la riga che fa riferimento alla riga eliminata viene eliminata dalla tabella esterna.

Per implementare l'opzione ON DELETE CASCADE, aggiungerla dopo la dichiarazione del vincolo Y FOREIGN KE:

ALTER TABLE Flight ADD FOREIGN KEY FK_AirplaneId (AirplaneId)
REFERENCES Airplane(AirplaneId)
ON DELETE CASCADE;

Si dovrebbe pensare di implementare l'opzione ON DELETE CASCADE con il vincolo FOREIGN KEY quando si vuole:

• La possibilità di rimuovere righe dalla tabella primaria senza alcuna restrizione.
• Eliminazione automatica delle righe della tabella esterna quando viene eliminata la riga di riferimento della tabella primaria.

SU CANCELLAZIONE LIMITARE

L'opzione ON DELETE RESTRICT non consente l'eliminazione delle righe della chiave primaria nella tabella primaria. Quando si tenta di eseguire tale azione, viene restituito un errore.

È utile per evitare modifiche indesiderate alle tabelle primarie ed esterne.

SU CANCELLAZIONE NESSUNA AZIONE

L'opzione ON DELETE NO ACTION è simile all'opzione ON DELETE RESTRICT, ma impedisce anche la cancellazione delle righe della tabella primaria e restituisce un errore se si tenta di farlo.

La differenza tra ON DELETE NO ACTION e ON DELETE RESTRICT è che in alcuni database ON DELETE NO ACTION può essere differita, cioè può essere rimandata a dopo la transazione in modo che l'eventuale errore venga lanciato dopo il completamento della transazione, cioè nel corso della transazione il valore della chiave esterna può essere corretto, altrimenti l'errore viene lanciato.

ON DELETE SET NULL

L'opzione ON DELETE SET NULL fa esattamente quello che dice. Quando una riga viene cancellata dalla tabella primaria, i valori delle colonne che vi fanno riferimento nella tabella esterna vengono impostati su NULL.

Vediamo un esempio qui sotto. Ecco di nuovo le due tabelle.

Rimuoviamo una riga dalla tabella primaria.

Ora, il valore della colonna AirplaneId della riga che fa riferimento alla riga eliminata è impostato su NULL nella tabella esterna.

L'opzione ON DELETE SET NULL può essere usata quando si vuole consentire l'eliminazione di una riga nella tabella primaria, ma non si vuole che venga propagata nella tabella esterna. Nella tabella straniera viene lasciata un'impronta dell'eliminazione (il valore contrassegnato da NULL).

ON DELETE SET DEFAULT

L'opzione ON DELETE SET DEFAULT è in qualche modo simile all'opzione ON DELETE SET NULL. In questo caso, dopo l'eliminazione della riga nella tabella primaria, si imposta il valore della colonna FK della tabella esterna sul valore predefinito invece che su NULL.

Si noti che è necessario specificare un valore predefinito per le colonne della chiave esterna.

Specifichiamo un valore predefinito per la colonna AirplaneId della tabella Flight della tabella:

ALTER TABLE Flight
ALTER AirplaneId
SET DEFAULT 'ABA340';

Ora possiamo usare l'opzione ON DELETE SET DEFAULT. Dopo l'eliminazione della riga della tabella primaria, si verificherà quanto segue:

Si dovrebbe usare l'opzione ON DELETE SET DEFAULT quando si vuole consentire l'eliminazione di righe nella tabella primaria. Il grado di controllo è dato dalla possibilità di scegliere quale valore viene inserito nella tabella esterna dopo l'eliminazione della riga nella tabella primaria.

Quando una riga di chiave primaria viene aggiornata

Le azioni offerte dal vincolo FOREIGN KEY sull'aggiornamento eseguito nella tabella primaria sono simili a quelle della cancellazione. Anche la sintassi è simile.

Sintassi di ON UPDATE

La dichiarazione dell'opzione ON UPDATE segue direttamente la dichiarazione del vincolo FOREIGN KEY.

ALTER TABLE Flight ADD FOREIGN KEY FK_AirplaneId (AirplaneId)
REFERENCES Airplane(AirplaneId)
ON UPDATE ;

Per impostazione predefinita, quando non viene specificata alcuna azione per il vincolo FOREIGN KEY, non è possibile modificare le righe della tabella primaria (genitore) se ci sono righe che fanno riferimento ad essa nella tabella esterna (figlio).

È possibile definire cosa succede all'aggiornamento delle righe della tabella primaria scegliendo una delle seguenti azioni:

• ON UPDATE CASCADE
• ON UPDATE RESTRICT
• ON UPDATE NO ACTION
• ON UPDATE SET NULL
• ON UPDATE SET DEFAULT

Esaminiamo ciascuna di esse.

SU CASCATA DI AGGIORNAMENTI

L'opzione ON UPDATE CASCADE consente di modificare i valori delle colonne PK della tabella primaria. Queste modifiche vengono poi propagate alla tabella esterna.

Vediamolo in azione usando le nostre tabelle Airplane e Flight e:

Modifichiamo i valori dell'ultima riga della tabella primaria.

Ora anche la riga che vi fa riferimento viene aggiornata.

Per implementare l'opzione ON UPDATE CASCADE, aggiungerla dopo la dichiarazione del vincolo FOREIGN KEY:

ALTER TABLE Flight ADD FOREIGN KEY FK_AirplaneId (AirplaneId)
REFERENCES Airplane(AirplaneId)
ON UPDATE CASCADE;

Questa opzione consente di modificare le colonne della chiave primaria della tabella primaria. Qualsiasi modifica viene poi propagata alle chiavi esterne.

SU AGGIORNAMENTO LIMITARE

Se si desidera impedire qualsiasi modifica alla PK della tabella primaria, usare l'opzione ON UPDATE RESTRICT.

Ogni volta che si tenta di modificare i valori della PK della tabella primaria, viene restituito un errore.

SU AGGIORNAMENTO NESSUNA AZIONE

L'opzione ON UPDATE NO ACTION è simile all'opzione ON UPDATE RESTRICT. Non sono consentite modifiche alla PK della tabella primaria.

La differenza è che alcuni database permettono di posticipare i controlli delle azioni fino al termine della transazione con ON UPDATE NO ACTION. Se i valori PK della tabella primaria vengono modificati al termine della transazione, viene lanciato un errore.

ON UPDATE SET NULL

L'opzione ON UPDATE SET NULL consente di modificare la PK della tabella primaria. In questi aggiornamenti, i valori della FK della tabella esterna sono impostati su NULL.

Vediamo un esempio di seguito, partendo dalle due tabelle:

Modifichiamo i valori dell'ultima riga della tabella primaria.

Ora la riga che vi fa riferimento è impostata su NULL.

SU AGGIORNAMENTO IMPOSTA PREDEFINITO

L'opzione ON UPDATE SET DEFAULT consente di modificare la PK della tabella primaria. Di conseguenza, i valori della chiave esterna vengono impostati al loro valore predefinito. È possibile utilizzare questa opzione solo se è stato dichiarato un valore predefinito per le colonne della chiave esterna.

Specifichiamo il valore predefinito per la colonna AirplaneId della tabella Flight della tabella:

ALTER TABLE Flight
ALTER AirplaneId
SET DEFAULT 'ABA340';

Ora possiamo usare l'opzione ON UPDATE SET DEFAULT. Dopo l'aggiornamento della riga della tabella primaria, si verificherà quanto segue:

Cardinalità del vincolo di chiave esterna

Un'altra caratteristica offerta dal vincolo FOREIGN KEY è la cardinalità. Impostando la cardinalità, si può decidere come le colonne della chiave esterna si relazionano con le colonne della chiave primaria. Le relazioni possono essere:

• Uno-a-uno (un valore di riga PK può comparire esattamente in una riga FK).
• Molti-a-uno (un valore di riga PK può comparire in una o più righe FK).
• Molti-a-molti (molti valori di riga PK possono apparire in molte righe FK).

Per implementare la relazione uno-a-uno, occorre dichiarare le colonne della chiave esterna come UNIQUE. In questo caso, la relazione è effettivamente (uno o zero)-uno, ma garantisce che ci sia al massimo una riga nella tabella esterna che si riferisce a una particolare riga della tabella primaria.

D'altra parte, se le colonne delle chiavi esterne della tabella esterna non sono dichiarate come UNIQUE, la relazione tra le colonne delle chiavi esterne e le colonne delle chiavi primarie è di tipo molti-a-uno: molte righe della tabella esterna possono riferirsi a una singola riga della tabella primaria.

La relazione molti-a-molti è implementata utilizzando una tabella separata che memorizza ogni coppia di righe correlate.

Notate che quando abbiamo creato questa relazione molti-a-molti, il collegamento diretto tra le tabelle Airplane e Flight si trasforma in una tabella separata che memorizza tutte le relazioni in coppie AirplaneId-FlightId.

La tabella AirplaneFlight tabella ha una chiave esterna (AirplaneId) che fa riferimento alla colonna AirplaneId della tabella. Airplane della tabella. Ha anche una chiave esterna (FlightId) che fa riferimento alla colonna FlightId della tabella. Flight tabella. Le chiavi Airplane e Flight sono ora collegate indirettamente tra loro.

Volete saperne di più sulle chiavi esterne in SQL?

Il vincolo FOREIGN KEY è fondamentale per la progettazione di un database relazionale. Ci permette di collegare i dati in base alle nostre esigenze. Poiché crea alcune dipendenze tra le colonne delle tabelle primarie e straniere, ci permette anche di decidere cosa fare ON UPDATE e ON DELETE delle azioni eseguite sulle righe della tabella primaria.

L'uso del vincolo FOREIGN KEY garantisce l'integrità, la correttezza e la compattezza dei dati. I valori utilizzati dalla tabella esterna devono esistere nella tabella primaria. Inoltre, si possono imporre regole sul tipo di relazione, cioè uno-a-uno, molti-a-uno o molti-a-molti.

Se volete saperne di più sulle chiavi esterne, consultate il nostro articolo su Che cos'è una chiave esterna in SQL?

Per saperne di più sulla struttura e sulla progettazione dei database, vi consiglio la nostra traccia Creazione della struttura del database, che comprende tutti gli elementi essenziali. Prima di approfondire il tema, potete leggere qui la traccia.