Chiave esterna di SQL Server: come crearla con un esempio

By: Fiona Marrone Fiona Marrone
Hours aggiornato

⚡ Riepilogo intelligente

In SQL Server, una chiave esterna garantisce l'integrità referenziale collegando una tabella figlia a una tabella padre. Ogni valore della chiave esterna deve già esistere nella chiave primaria della tabella padre a cui fa riferimento.

  • 🔗 Che cos'è una chiave esterna? Una chiave esterna collega una tabella figlia a una tabella padre e garantisce l'integrità referenziale tra di esse.
  • 👪 Genitore e figlio: La tabella a cui si fa riferimento è la tabella padre; la tabella che contiene la chiave esterna è la tabella figlia, che punta alla chiave primaria della tabella padre.
  • 🖱️ Due metodi di creazione: Sia le relazioni di SQL Server Management Studio che la clausola CREATE TABLE … FOREIGN KEY … REFERENCES di T-SQL definiscono una chiave esterna.
  • ➕ Aggiungi a una tabella esistente: ALTER TABLE … ADD CONSTRAINT … FOREIGN KEY aggiunge la relazione a una tabella già esistente.
  • 🔄 Azioni di riferimento: Le clausole ON DELETE e ON UPDATE controllano le righe figlie con NO ACTION, CASCADE, SET NULL o SET DEFAULT.
  • ✅ Integrity controllare: L'inserimento di una riga figlia la cui chiave non ha una riga padre corrispondente viene rifiutato, mantieniping i dati sono coerenti.
Scopri di più

Chiave esterna di SQL Server: come crearla in SQL Server con un esempio

Cos'è una CHIAVE ESTERA?

Una chiave esterna fornisce un modo per imporre l'integrità referenziale all'interno SQL ServerIn parole semplici, una chiave esterna garantisce che i valori presenti in una tabella debbano essere presenti anche in un'altra tabella.

Regole per la CHIAVE ESTERA

  • Nei campi delle chiavi esterne SQL è consentito il valore NULL.
  • La tabella a cui si fa riferimento è chiamata tabella padre.
  • La tabella con la chiave esterna è chiamata tabella figlia.
  • La chiave esterna nella tabella figlia fa riferimento a chiave primaria nella tabella principale.
  • Questo rapporto genitore-figlio rafforza la regola nota come "integrità referenziale".

Il diagramma seguente riassume tutti i punti sopra descritti relativi alla chiave esterna.

Diagramma di una chiave esterna che collega una tabella figlia alla chiave primaria della tabella padre.

Come creare una CHIAVE ESTERA in SQL

È possibile creare una chiave esterna in SQL Server in due modi:

SQL Server Management Studio

Tabella principale: Supponiamo di avere una tabella principale esistente chiamata 'Course'. Course_ID e Course_name sono due colonne, con Course_ID come chiave primaria.

Tabella principale Course con chiave primaria Course_Id e colonne Course_name

Tabella figlia: Dobbiamo creare la seconda tabella come tabella figlia. Le sue due colonne saranno 'Course_ID' e 'Course_Strength'. Tuttavia, 'Course_ID' sarà la chiave esterna.

Passaggio 1) Fare clic con il pulsante destro del mouse su Tabelle > Nuovo > Tabella…

In SQL Server Management Studio, fai clic con il pulsante destro del mouse su Tabelle, quindi su Nuovo e infine su Tabella.

Passaggio 2) Inserire due nomi di colonna: 'Course_ID' e 'Course_Strength'. Fare clic con il pulsante destro del mouse sulla colonna 'Course_ID', quindi fare clic su Relazione.

Nuove colonne della tabella secondaria Course_ID e Course_Strength con il menu Relazione

Passaggio 3) In 'Relazioni chiave esterna', fare clic su 'Aggiungi'.

Finestra di dialogo Relazioni chiave esterna con il pulsante Aggiungi

Passaggio 4) In 'Specifiche di tabelle e colonne', fare clic sull'icona '…'.

Campo di specifica di tabelle e colonne con il pulsante con i puntini di sospensione

Passaggio 5) Selezionare "COURSE" come "Tabella chiave primaria" e la nuova tabella da creare come "Tabella chiave esterna" dal menu a tendina.

Selezionare CORSO come tabella chiave primaria nella finestra di dialogo delle relazioni

Passaggio 6) Per la 'Tabella chiave primaria', selezionare la colonna 'Course_Id' come colonna della tabella chiave primaria.

Per la "Tabella delle chiavi esterne", selezionare la colonna "Course_Id" come colonna della tabella delle chiavi esterne. Fare clic su OK.

Mappaping Course_Id come colonna chiave primaria e chiave esterna

Passaggio 7) Fare clic su Aggiungi.

Fare clic su Aggiungi per confermare la relazione di chiave esterna

Passaggio 8) Assegnare alla tabella il nome 'Course_Strength' e fare clic su OK.

Assegnare alla tabella secondaria il nome Course_Strength e fare clic su OK

Risultato: Abbiamo stabilito una relazione padre-figlio tra 'Course' e 'Course_Strength'.

Relazione genitore-figlio stabilita tra Corso e Forza del Corso.

ARTICOLI CORRELATI

T-SQL: Creare una tabella padre-figlio utilizzando T-SQL

Tabella padre: Consideriamo di avere già una tabella padre denominata 'Course'. Course_ID e Course_name sono due colonne, con Course_ID come chiave primaria.

Tabella padre esistente Course con Course_Id come chiave primaria

Tabella figlia: Dobbiamo creare una seconda tabella come tabella figlia con il nome 'Course_Strength_TSQL'. Le sue due colonne saranno 'Course_ID' e 'Course_Strength'. Tuttavia, 'Course_ID' sarà la chiave esterna.

Di seguito è riportata la sintassi per creare una tabella con una CHIAVE ESTERNA.

Sintassi:

CREATE TABLE childTable
(
  column_1 datatype [ NULL |NOT NULL ],
  column_2 datatype [ NULL |NOT NULL ],
  ...

  CONSTRAINT fkey_name
    FOREIGN KEY (child_column1, child_column2, ... child_column_n)
    REFERENCES parentTable (parent_column1, parent_column2, ... parent_column_n)
    [ ON DELETE { NO ACTION |CASCADE |SET NULL |SET DEFAULT } ]
    [ ON UPDATE { NO ACTION |CASCADE |SET NULL |SET DEFAULT } ] 
);

Ecco una descrizione dei parametri di cui sopra:

  • childTable è il nome della tabella da creare.
  • column_1 e column_2 sono le colonne da aggiungere alla tabella.
  • fkey_name è il nome del vincolo di chiave esterna da creare.
  • child_column1, child_column2 … child_column_n sono le colonne della tabella figlia che fanno riferimento alla chiave primaria nella tabella padre.
  • parentTable è il nome della tabella padre la cui chiave è referenziata nella tabella figlia.
  • parent_column1, parent_column2 … parent_column_n sono le colonne che costituiscono la chiave primaria della tabella padre.
  • ON DELETE è un parametro opzionale che specifica cosa accade ai dati figlio dopo l'eliminazione dei dati padre. I valori possibili sono NO ACTION, SET NULL, CASCADE o SET DEFAULT.
  • ON UPDATE è un parametro opzionale che specifica cosa accade ai dati del figlio dopo l'aggiornamento dei dati del padre. I valori possibili sono NO ACTION, SET NULL, CASCADE o SET DEFAULT.
  • NESSUNA AZIONE significa che non accade nulla ai dati del figlio dopo l'aggiornamento o la cancellazione dei dati del padre.
  • CASCADE significa che i dati del figlio vengono eliminati o aggiornati dopo che i dati del padre sono stati eliminati o aggiornati.
  • SET NULL significa che i dati del figlio vengono impostati su null dopo che i dati del genitore sono stati aggiornati o eliminati.
  • L'opzione IMPOSTA DEFAULT significa che i dati del figlio vengono ripristinati al loro valore predefinito dopo un aggiornamento o una cancellazione dei dati del padre.

Vediamo un esempio di chiave esterna che crea una tabella con una colonna come CHIAVE ESTERNA, utilizzando una tipo di dati per ogni colonna.

Esempio di chiave esterna in SQL

Query:

CREATE TABLE Course_Strength_TSQL
(
Course_ID Int,
Course_Strength Varchar(20) 
CONSTRAINT FK FOREIGN KEY (Course_ID)
REFERENCES COURSE (Course_ID)	
)

Passaggio 1) Eseguire la query facendo clic su Esegui.

Esecuzione della query CREATE TABLE che definisce la chiave esterna Course_ID

Risultato: Abbiamo stabilito una relazione padre-figlio tra 'Course' e 'Course_Strength_TSQL'.

Relazione padre-figlio creata tra Course e Course_Strength_TSQL

Utilizzando ALTER TABLE

Ora impareremo come aggiungere una chiave esterna in SQL Server a una tabella già esistente utilizzando l'istruzione ALTER TABLE. Useremo la sintassi riportata di seguito:

ALTER TABLE childTable
ADD CONSTRAINT fkey_name
    FOREIGN KEY (child_column1, child_column2, ... child_column_n)
    REFERENCES parentTable (parent_column1, parent_column2, ... parent_column_n);

Ecco una descrizione dei parametri utilizzati sopra:

  • childTable è il nome della tabella da creare.
  • column_1 e column_2 sono le colonne da aggiungere alla tabella.
  • fkey_name è il nome del vincolo di chiave esterna da creare.
  • child_column1, child_column2 … child_column_n sono le colonne della tabella figlia che fanno riferimento alla chiave primaria nella tabella padre.
  • parentTable è il nome della tabella padre la cui chiave è referenziata nella tabella figlia.
  • parent_column1, parent_column2 … parent_column_n sono le colonne che costituiscono la chiave primaria della tabella padre.

Esempio di ALTER TABLE aggiunta di chiave esterna:

ALTER TABLE department
ADD CONSTRAINT fkey_student_admission
    FOREIGN KEY (admission)
    REFERENCES students (admission);

Abbiamo creato una chiave esterna denominata fkey_student_admission nella tabella del dipartimento. Questa chiave esterna fa riferimento alla colonna di ammissione della tabella degli studenti.

Esempio di query CHIAVE ESTERA

Innanzitutto, diamo un'occhiata ai dati della nostra tabella principale, COURSE.

Query:

SELECT * from COURSE;

Risultato della selezione che mostra i dati del corso nella tabella padre

Ora proviamo a inserire alcune righe nella tabella secondaria 'Course_Strength_TSQL'. Cercheremo di inserire due tipi di righe:

  • Il primo tipo, per cui Course_Id nella tabella figlia esiste in Course_Id nella tabella padre, ovvero Course_Id = 1 e 2.
  • Il secondo tipo, per il quale Course_Id nella tabella figlia non esiste in Course_Id nella tabella padre, ovvero Course_Id = 5.

Query:

Insert into COURSE_STRENGTH values (1,'SQL');
Insert into COURSE_STRENGTH values (2,'Python');
Insert into COURSE_STRENGTH values (5,'PERL');

Inserimento di righe figlie, incluso Course_ID 5 che non ha un genitore corrispondente

Risultato: Eseguiamo insieme la query per visualizzare le tabelle padre e figlio.

Le righe con Course_ID 1 e 2 sono presenti nella tabella Course_Strength. Course_ID 5, tuttavia, costituisce un'eccezione, poiché non ha una riga corrispondente nella tabella principale.

Confronto tra tabelle padre e figlio; Course_ID 5 viola l'integrità referenziale

DOMANDE FREQUENTI

Una chiave primaria identifica in modo univoco ogni riga all'interno della propria tabella e non può essere NULL. Una chiave esterna fa riferimento a quella chiave primaria da un'altra tabella per garantire l'integrità referenziale. chiave primaria rispetto alla chiave esterna Il confronto chiarisce ogni differenza.

Sì. Una chiave esterna può fare riferimento a una chiave primaria o a qualsiasi colonna che abbia un vincolo UNIQUE nella tabella padre. La colonna a cui si fa riferimento deve contenere valori univoci, in modo che ogni riga della tabella figlia corrisponda esattamente a una riga della tabella padre.

ON DELETE CASCADE elimina automaticamente le righe figlie corrispondenti ogni volta che viene eliminata la riga padre, mantenendoping le tabelle sono coerenti. Le alternative sono SET NULL, che cancella la chiave esterna figlia, e NO ACTION, che blocca l'eliminazione.

Sì. Una chiave esterna autoreferenziale punta a una chiave primaria nella stessa tabella, che modella gerarchie come ad esempio una riga dipendente che fa riferimento al suo responsabile. Per le autoreferenziali, SQL Server consiglia di utilizzare ON DELETE NO ACTION per evitare cicli a cascata.

Sì, a meno che la colonna non sia dichiarata NOT NULL. Una chiave esterna NULL significa che la riga figlia non è ancora collegata ad alcuna riga padre e SQL Server ignora il controllo referenziale per quel valore NULL.

Eseguire ALTER TABLE child_table DROP CONSTRAINT fkey_name. È necessario fornire il nome del vincolo, che si trova in sys.foreign_keys. Eliminareping La chiave esterna elimina la relazione, ma lascia inalterate entrambe le tabelle e i relativi dati.

Sì. Le serrature scorrevoli portatili e i catenacci a superficie possono essere usati per mettere in sicurezza una porta a scomparsa dall'esterno. Alcuni kit con catena di sicurezza consentono anche il bloccaggio esterno con chiave o manopola girevole. Copilota GitHub È possibile scrivere vincoli FOREIGN KEY all'interno di istruzioni CREATE TABLE o ALTER TABLE tramite un prompt in linguaggio naturale e suggerire la tabella padre e la colonna di riferimento. Prima di eseguire lo script, è sempre necessario rivedere le chiavi, le azioni di riferimento e i tipi di dati.

Gli strumenti di intelligenza artificiale e apprendimento automatico analizzano i dati campione e i modelli di query per suggerire quali colonne dovrebbero diventare chiavi esterne, rilevare relazioni mancanti o orfane e raccomandare azioni ON DELETE appropriate. Lo sviluppatore esamina ogni suggerimento prima di applicarlo.

Riassumi questo post con: