Se possiedi un’impresa, devi aver incontrato il valore e la necessità di dati nella tua attività. Avere i mezzi per archiviare e manipolare i database aggiunge più valore all’azienda.
I database sono organizzati secondo una particolare convenzione e consentono di strutturare i dati in connessioni, portandoci ai database relazionali, che sono stati adottati come forma di gestione dei dati sin dagli anni ’70. E nel mercato odierno, i database relazionali sono preferiti per le loro capacità nella manipolazione dei dati.
Sebbene ci siano molti database relazionali disponibili là fuori, MySQL è arrivato al primo posto, classificandosi come il numero due al mondo, secondo Statista, a partire da gennaio 2022.
Nel server SQL, i vincoli sono regole e limitazioni predefinite applicate in una o più colonne; sono legati ai valori nella colonna e aiutano a mantenere l’integrità, l’accuratezza e l’affidabilità dei dati delle colonne specificate.
In poche parole, solo i dati che soddisfano la regola del vincolo vengono inseriti correttamente nella colonna. L’operazione di inserimento viene terminata se i dati non soddisfano i criteri.
Questo post presuppone che tu abbia incontrato database relazionali, in particolare MySQL, e non vedi l’ora di rafforzare le tue conoscenze nel dominio. In definitiva, condividerò alcuni suggerimenti per interagire con i vincoli di chiave esterna.
Sommario:
Vincoli di chiave primaria: un riepilogo
Una tabella in SQL comporta una o più colonne contenenti valori chiave che individuano con precisione ogni riga nei sistemi. La colonna o le colonne denominate chiave primaria (PK) della tabella hanno il ruolo di imporre l’integrità dell’entità della tabella. I vincoli di chiave primaria garantiscono dati univoci e sono spesso definiti su una colonna Identity.
Dopo aver specificato i vincoli di chiave primaria per la tabella, il motore di database impone automaticamente l’univocità dei dati generando indici univoci per ciascuna delle colonne primarie. Le chiavi primarie offrono un vantaggio estremo se utilizzate nelle query fornendo un rapido accesso ai dati.
Se i vincoli di chiave primaria sono definiti su più colonne, si parla di chiave primaria composita o composta. E in questo caso, ogni colonna della chiave primaria può contenere valori duplicati. Tuttavia, i valori combinati di tutte le colonne nella chiave primaria devono essere univoci.
Un buon esempio è il caso in cui hai una tabella con le colonne “id”, “names” e “age”. Quando definisci il suo vincolo di chiave primaria sulla combinazione di `id` e `names`, puoi avere istanze duplicate dei valori `id` o `names`. Tuttavia, ogni combinazione deve essere univoca per evitare righe duplicate. Quindi, potresti avere record con `id=1` e `name=Walter`, e `age-22 “e `id=1`, `name=Henry` e `age=27`, ma non puoi avere altri record con `id=1` e `name=Walter` perché la combinazione non è univoca.
Ecco alcuni aspetti essenziali da conoscere:
Vincoli di chiave esterna: una carrellata
Una chiave esterna (FK) comporta una colonna o una combinazione di più utilizzate per creare e associare un collegamento tra due tabelle e gestisce i dati da memorizzare in una tabella di chiave esterna.
Un riferimento a una chiave esterna comporta la creazione di un collegamento tra due tabelle; quando una o più colonne che contengono la chiave primaria per un’altra tabella vengono referenziate da una o più colonne in una tabella diversa.
Nello scenario di riferimento della chiave esterna, viene creata una connessione tra due tabelle quando una colonna o colonne che contengono chiavi primarie in una tabella sono referenziate da colonne in un’altra.
In un caso d’uso pratico, puoi avere una tabella, Sales.SalesOrderHeader, con una chiave esterna che si collega a un’altra tabella, Sales.Person, perché esiste una relazione logica tra i venditori e gli ordini di vendita.
Qui, SalesPersonID nella colonna SalesOrderHeader si fonde con la colonna chiave primaria della tabella SalesPerson. La chiave esterna della tabella SalesPerson è la colonna SalesPersonID in SalesOrderHeader.
Questa relazione definisce una regola: un valore SalesPersonID non può trovarsi nella tabella SalesOrderHeader se non esiste nella tabella SalesPerson.
Una tabella può fare riferimento a un massimo di altre 253 colonne e tabelle come chiavi esterne, chiamate in alternativa riferimenti in uscita. Dal 2016, il server SQL ha aumentato il numero di tabelle e colonne a cui è possibile fare riferimento in una singola tabella, noto anche come riferimenti in entrata, da 253 a 10000. Tuttavia, l’aumento comporta alcune restrizioni:
Quali sono i vantaggi delle chiavi esterne?
Come accennato in precedenza, i vincoli di chiave esterna svolgono un ruolo essenziale nella salvaguardia dell’integrità e della coerenza dei dati nel database relazionale. Ecco una ripartizione dei motivi per cui i vincoli di chiave esterna sono essenziali.
Indici dei vincoli di chiave esterna
I vincoli di chiave esterna non creano automaticamente indici corrispondenti come il primario. È possibile creare manualmente indici per vincoli di chiave esterna; è utile per i seguenti motivi.
- Le colonne di chiave esterna vengono spesso utilizzate nei criteri di join quando si combinano i dati delle tabelle correlate nelle query facendo corrispondere le colonne legate al vincolo. Gli indici aiutano il database a trovare i dati associati in una tabella esterna.
- Se si modificano i vincoli di chiave primaria, vengono verificati con quelli esterni nelle tabelle correlate.
Non è obbligatorio creare indici. È comunque possibile combinare i dati di due tabelle senza specificare i vincoli di chiave primaria ed esterna. Tuttavia, l’aggiunta di vincoli di chiave esterna ottimizza le tabelle e le combina in una query che soddisfa i suoi criteri di utilizzo delle chiavi. Se si modificano i vincoli di chiave primaria, vengono verificati con quelli esterni in correlati.
Suggerimenti per creare vincoli di chiave esterna in SQL
Hai già dedicato molto tempo alla speculazione; ha risposto al perché. Spostiamo la nostra attenzione e restringiamo il campo alle tattiche per creare vincoli di chiave esterna; rispondi al come.
Un campo “Chiave esterna” in una tabella fa riferimento alla “Chiave primaria” di un’altra. La tabella con la chiave primaria è la tua tabella padre. E la tabella con la chiave esterna è chiamata tabella figlio. Immergiamoci.
Creazione di una chiave esterna durante la creazione di una tabella
Quando crei una tabella, puoi anche creare un vincolo di chiave esterna per mantenere l’integrità referenziale. Ecco come farlo:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
Il codice sopra crea una tabella chiamata “orders” con la chiave intera primaria “order_id”, un altro numero intero “customer_id” e la data “order_date”. In questo caso, il vincolo FOREIGN KEY viene aggiunto alla colonna “customer_id” e fa riferimento a “customer_id” nella tabella “customers”.
Creazione di una chiave esterna dopo aver creato una tabella
Supponiamo di aver già creato una tabella e di voler aggiungere un vincolo di chiave esterna; usa l’istruzione `ALTER TABLE` nel tuo codice. Guarda lo snippet di codice qui sotto.
ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
In questo caso, hai aggiunto una colonna ‘customer_id’ del vincolo di chiave esterna nella tabella ‘orders’ per fare riferimento alla colonna ‘customer_id’ nella tabella ‘customers’.
Creazione di una chiave esterna senza controllare i dati esistenti
Quando aggiungi un vincolo di chiave esterna a una tabella, il database controlla automaticamente i dati esistenti per garantire la coerenza con il vincolo. Tuttavia, se sai che i dati sono coerenti e vuoi aggiungere un vincolo senza il controllo di coerenza, ecco come farlo.
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) NOT VALIDATE;
Il comando NOT VALIDATE dice al database di non controllare i dati esistenti. Questo caso particolare è utile in casi specifici. Ad esempio, quando si dispone di enormi dati e si desidera completare il processo di convalida.
Creazione di una chiave esterna tramite DELETE/UPDATE
Quando si creano vincoli di chiave esterna, è possibile indirizzare l’azione da intraprendere nei casi in cui la riga di riferimento viene aggiornata o eliminata. In questo caso, si utilizzano vincoli di integrità referenziale a cascata per dettare le azioni da intraprendere. Loro includono:
#1. NESSUNA AZIONE
Come con molti altri database, la regola “NO ACTION” è il comportamento predefinito quando si crea un vincolo di chiave esterna. Ciò significa che non viene intrapresa alcuna azione quando la riga di riferimento viene eliminata o aggiornata.
Il Motore di database genera un errore se il vincolo di chiave esterna viene violato. Tuttavia, questo non è consigliato perché può portare a problemi di integrità referenziale poiché è necessario applicare il vincolo di chiave esterna. Ecco un esempio di come farlo:
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE NO ACTION ON UPDATE NO ACTION;
#2. CASCATA
La regola “CASCADE” è un’altra opzione per le azioni “ON DELETE” e “ON UPDATE” durante la creazione di vincoli di chiave esterna. Quando messo in atto, significa che ogni volta che una riga viene aggiornata o eliminata nelle tabelle padre, le righe di riferimento vengono aggiornate o eliminate di conseguenza. Questa tecnica è potente quando si mantiene l’integrità referenziale. Ecco un esempio:
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE ON UPDATE CASCADE;
Dovresti essere attento quando usi questa regola, poiché può causare conseguenze indesiderabili se non usata con attenzione. Dovresti evitare di eliminare accidentalmente troppi dati o creare riferimenti circolari. Utilizzare quindi questa opzione solo se necessario e con cautela.
Ci sono alcune regole sull’uso di CASCADE:
- Non è possibile specificare CASCADE se una colonna timestamp fa parte della chiave esterna o di riferimento.
- Se la tua tabella ha un trigger INSTEAD OF DELETE, non puoi specificare ON DELETED CASCADE.
- Non puoi specificare ON UPDATE CASCADE se la tua tabella ha un trigger INSTEAD OF UPDATE.
#3. IMPOSTA NULLA
Quando elimini o aggiorni una riga corrispondente nella tabella padre, tutti i valori che compongono la chiave esterna vengono impostati su null. Questa regola di vincolo richiede che le colonne della chiave esterna siano nullable per l’esecuzione e non possono essere specificate per le tabelle con trigger INSTEAD OF UPDATE. Ecco un esempio di come farlo.
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE SET NULL ON UPDATE SET NULL
In questo caso, hai impostato la colonna della chiave esterna ‘customer_id’ nella tabella “orders” su null se la riga corrispondente nella tabella “customers” viene eliminata o aggiornata.
#4. IMPOSTA DEFAULT
Qui, stai impostando tutti i valori che rendono predefinita la chiave esterna a condizione che la riga a cui si fa riferimento nella tabella padre venga aggiornata o eliminata.
Questo vincolo viene eseguito se tutte le colonne di chiavi esterne hanno definizioni predefinite. Se una colonna ammette valori Null, il relativo valore predefinito è impostato su NULL. Si noti che questa opzione non può essere specificata per le tabelle con trigger INSTEAD OF UPDATE. Ecco un esempio:
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
Nel caso precedente, hai impostato ‘customer_id’ nella tabella “orders” al suo valore predefinito, che si verifica quando la riga corrispondente nella tabella “customers” viene eliminata o aggiornata.
Parole finali
In questa guida, hai avuto un aggiornamento sui vincoli di chiave primaria e hai approfondito i vincoli di chiave esterna. Hai anche incontrato diverse tecniche per creare vincoli di chiave esterna. E mentre ci sono molti modi per creare vincoli di chiave esterna, questo post ha svelato i metodi.
E sperando che tu abbia colto nuove tecniche; non sei limitato a combinarli. Ad esempio, i metodi di vincolo CASCADE, SET NULL, SET DEFAULT e NO ACTION possono essere combinati su tabelle con relazioni referenziali.
Se il tuo tavolo non incontra NESSUNA AZIONE, ritorna ad altre regole di vincolo. In altri casi, un’azione DELETE può attivare una combinazione di queste regole e la regola NO ACTION verrà eseguita per ultima.
Successivamente, controlla il cheat sheet di SQL.