I vincoli di chiave primaria ed esterna

• 07/25/2017
• 9 minuti per leggere
• • s
  • M
  • M
  • c
  • J
  • +1

Si applica a: QL Server 2016 (13.x) e successivi Azure SQL Database Azure SQL Managed Instance

Le chiavi primarie e le chiavi esterne sono due tipi di vincoli che possono essere usati per applicare l’integrità dei dati nelle tabelle di SQL Server. Si tratta di importanti oggetti del database.

Questo argomento contiene le seguenti sezioni.

Costrizioni a chiave primaria

Costrizioni a chiave esterna

Compiti correlati

Costrizioni a chiave primaria

Una tabella ha tipicamente una colonna o una combinazione di colonne che contengono valori che identificano in modo unico ogni riga della tabella. Questa colonna, o colonne, è chiamata chiave primaria (PK) della tabella e fa rispettare l’integrità dell’entità della tabella. Poiché i vincoli di chiave primaria garantiscono l’unicità dei dati, sono spesso definiti su una colonna di identità.

Quando si specifica un vincolo di chiave primaria per una tabella, il motore di database applica l’unicità dei dati creando automaticamente un indice unico per le colonne della chiave primaria. Questo indice permette anche un accesso veloce ai dati quando la chiave primaria è usata nelle query. Se un vincolo di chiave primaria è definito su più di una colonna, i valori possono essere duplicati all’interno di una colonna, ma ogni combinazione di valori di tutte le colonne nella definizione del vincolo di chiave primaria deve essere unica.

Come mostrato nella seguente illustrazione, le colonne ProductID e VendorID nella tabella Purchasing.ProductVendor formano un vincolo di chiave primaria composto per questa tabella. Questo fa sì che ogni riga nella tabella ProductVendor abbia una combinazione unica di ProductID e VendorID. Questo previene l’inserimento di righe duplicate.

• Una tabella può contenere un solo vincolo di chiave primaria.

• Una chiave primaria non può superare 16 colonne e una lunghezza totale di 900 byte.

• L’indice generato da un vincolo di chiave primaria non può far sì che il numero di indici sulla tabella superi 999 indici non clusterati e 1 indice clusterato.

• Se non è specificato clustered o nonclustered per un vincolo di chiave primaria, viene usato clustered se non c’è un indice clustered sulla tabella.

• Tutte le colonne definite all’interno di un vincolo di chiave primaria devono essere definite come non nulla. Se la nullability non è specificata, tutte le colonne che partecipano a un vincolo di chiave primaria hanno la loro nullability impostata su not null.

• Se una chiave primaria è definita su una colonna di tipo CLR definito dall’utente, l’implementazione del tipo deve supportare l’ordinamento binario.

Vincoli di chiave esterna

Una chiave esterna (FK) è una colonna o una combinazione di colonne che è usata per stabilire e applicare un collegamento tra i dati in due tabelle per controllare i dati che possono essere memorizzati nella tabella con chiave esterna. In un riferimento a chiave esterna, si crea un collegamento tra due tabelle quando la colonna o le colonne che contengono il valore di chiave primaria per una tabella sono referenziate dalla colonna o dalle colonne di un’altra tabella. Questa colonna diventa una chiave esterna nella seconda tabella.

Per esempio, la tabella Sales.SalesOrderHeader ha un collegamento a chiave esterna alla tabella Sales.SalesPerson perché c’è una relazione logica tra ordini di vendita e venditori. La colonna SalesPersonID nella tabella SalesOrderHeader corrisponde alla colonna chiave primaria della tabella SalesPerson. La colonna SalesPersonID nella tabella SalesOrderHeader è la chiave esterna della tabella SalesPerson. Creando questa relazione di chiave esterna, un valore per SalesPersonID non può essere inserito nella tabella SalesOrderHeader se non esiste già nella tabella SalesPerson.

Una tabella può fare riferimento a un massimo di 253 altre tabelle e colonne come chiavi esterne (riferimenti in uscita). SQL Server 2016 (13.x) aumenta il limite per il numero di altre tabelle e colonne che possono fare riferimento a colonne in una singola tabella (riferimenti in entrata), da 253 a 10.000. (Richiede almeno il livello di compatibilità 130.) L’aumento ha le seguenti restrizioni:

• Più di 253 riferimenti a chiavi esterne sono supportati solo per operazioni DELETE DML. Le operazioni UPDATE e MERGE non sono supportate.

• Una tabella con un riferimento di chiave esterna a se stessa è ancora limitata a 253 riferimenti di chiave esterna.

• I riferimenti di chiave esterna superiori a 253 non sono attualmente disponibili per indici columnstore, tabelle ottimizzate per la memoria, Stretch Database, o tabelle a chiave esterna partizionate.

Indici su vincoli a chiave esterna

A differenza dei vincoli a chiave primaria, la creazione di un vincolo a chiave esterna non crea automaticamente un indice corrispondente. Tuttavia, la creazione manuale di un indice su una chiave esterna è spesso utile per le seguenti ragioni:

• Le colonne della chiave esterna sono spesso usate nei criteri di unione quando i dati di tabelle correlate sono combinati nelle query facendo corrispondere la colonna o le colonne nel vincolo di chiave esterna di una tabella con la colonna o le colonne della chiave primaria o unica dell’altra tabella. Un indice permette al motore del database di trovare rapidamente i dati correlati nella tabella a chiave esterna. Tuttavia, la creazione di questo indice non è necessaria. I dati di due tabelle correlate possono essere combinati anche se non sono definiti vincoli di chiave primaria o di chiave esterna tra le tabelle, ma una relazione di chiave esterna tra due tabelle indica che le due tabelle sono state ottimizzate per essere combinate in una query che utilizza le chiavi come criteri.

• Le modifiche ai vincoli di chiave primaria sono controllate con i vincoli di chiave esterna nelle tabelle correlate.

Integrità referenziale

Anche se lo scopo principale di un vincolo di chiave esterna è di controllare i dati che possono essere memorizzati nella tabella di chiave esterna, esso controlla anche le modifiche ai dati nella tabella di chiave primaria. Per esempio, se la riga di un venditore viene cancellata dalla tabella Sales.SalesPerson, e l’ID del venditore è usato per gli ordini di vendita nella tabella Sales.SalesOrderHeader, l’integrità relazionale tra le due tabelle è rotta; gli ordini di vendita del venditore cancellato sono orfani nella tabella SalesOrderHeader senza un collegamento ai dati nella tabella SalesPerson.

Un vincolo di chiave esterna previene questa situazione. Il vincolo fa rispettare l’integrità referenziale garantendo che non si possano fare delle modifiche ai dati della tabella a chiave primaria se queste modifiche invalidano il collegamento ai dati della tabella a chiave esterna. Se viene fatto un tentativo di cancellare la riga in una tabella a chiave primaria o di cambiare un valore di chiave primaria, l’azione fallirà quando il valore di chiave primaria cancellato o cambiato corrisponde a un valore nel vincolo di chiave esterna di un’altra tabella. Per cambiare o cancellare con successo una riga in un vincolo di chiave esterna, devi prima cancellare i dati della chiave esterna nella tabella della chiave esterna o cambiare i dati della chiave esterna nella tabella della chiave esterna, che collega la chiave esterna a diversi dati della chiave primaria.

Integrità referenziale a cascata

Utilizzando i vincoli di integrità referenziale a cascata, puoi definire le azioni che il motore del database intraprende quando un utente cerca di cancellare o aggiornare una chiave alla quale puntano le chiavi esterne esistenti. Si possono definire le seguenti azioni a cascata.

NO ACTION
Il motore di database solleva un errore e l’azione di cancellazione o aggiornamento della riga nella tabella padre viene riportata indietro.

CASCADE
Le righe corrispondenti vengono aggiornate o cancellate nella tabella di riferimento quando quella riga viene aggiornata o cancellata nella tabella padre. CASCADE non può essere specificato se una colonna timestamp fa parte della chiave esterna o della chiave di riferimento. ON DELETE CASCADE non può essere specificato per una tabella che ha un trigger INSTEAD OF DELETE. ON UPDATE CASCADE non può essere specificato per le tabelle che hanno un trigger INSTEAD OF UPDATE.

SET NULL
Tutti i valori che compongono la chiave esterna sono impostati a NULL quando la riga corrispondente nella tabella madre viene aggiornata o cancellata. Affinché questo vincolo venga eseguito, le colonne della chiave esterna devono essere nulle. Non può essere specificato per le tabelle che hanno trigger INSTEAD OF UPDATE.

SET DEFAULT
Tutti i valori che compongono la chiave esterna sono impostati ai loro valori predefiniti se la riga corrispondente nella tabella padre viene aggiornata o cancellata. Affinché questo vincolo venga eseguito, tutte le colonne della chiave esterna devono avere definizioni predefinite. Se una colonna è nulla, e non c’è un valore predefinito esplicito impostato, NULL diventa il valore predefinito implicito della colonna. Non può essere specificato per tabelle che hanno trigger INSTEAD OF UPDATE.

CASCADE, SET NULL, SET DEFAULT e NO ACTION possono essere combinati su tabelle che hanno relazioni referenziali tra loro. Se il motore di database incontra NO ACTION, si ferma e fa rollback delle azioni CASCADE, SET NULL e SET DEFAULT correlate. Quando un’istruzione DELETE causa una combinazione di azioni CASCADE, SET NULL, SET DEFAULT e NO ACTION, tutte le azioni CASCADE, SET NULL e SET DEFAULT sono applicate prima che il Database Engine controlli qualsiasi NO ACTION.

Trigger e azioni referenziali a cascata

Le azioni referenziali a cascata attivano i trigger AFTER UPDATE o AFTER DELETE nel modo seguente:

• Tutte le azioni referenziali a cascata direttamente causate dalla DELETE o UPDATE originale vengono eseguite per prime.

• Se ci sono trigger AFTER definiti sulle tabelle interessate, questi trigger scattano dopo che tutte le azioni a cascata sono state eseguite. Questi trigger scattano nell’ordine opposto all’azione a cascata. Se ci sono più trigger su una singola tabella, si attivano in ordine casuale, a meno che non ci sia un primo o un ultimo trigger dedicato per la tabella. Questo ordine è specificato utilizzando sp_settriggerorder.

• Se più catene a cascata hanno origine dalla tabella che era il bersaglio diretto di un’azione UPDATE o DELETE, l’ordine in cui queste catene sparano i loro rispettivi trigger non è specificato. Tuttavia, una catena spara sempre tutti i suoi trigger prima che un’altra catena inizi a sparare.

• Un trigger AFTER sulla tabella che è l’obiettivo diretto di un’azione UPDATE o DELETE scatta indipendentemente dal fatto che ci siano righe interessate. Non ci sono altre tabelle interessate dal cascading in questo caso.

• Se uno qualsiasi dei trigger precedenti esegue operazioni UPDATE o DELETE su altre tabelle, queste azioni possono avviare catene secondarie a cascata. Queste catene secondarie sono processate per ogni operazione UPDATE o DELETE alla volta dopo che tutti i trigger di tutte le catene primarie si sono attivati. Questo processo può essere ripetuto ricorsivamente per le successive operazioni UPDATE o DELETE.

• Performare CREATE, ALTER, DELETE, o altre operazioni DDL (Data Definition Language) all’interno dei trigger può causare l’innesco di trigger DDL. Questo può successivamente eseguire operazioni DELETE o UPDATE che avviano ulteriori catene a cascata e trigger.

• Se viene generato un errore all’interno di una particolare catena di azioni referenziali a cascata, viene generato un errore, nessun trigger AFTER viene sparato in quella catena, e l’operazione DELETE o UPDATE che ha creato la catena viene riportata indietro.

• Una tabella che ha un trigger INSTEAD OF non può avere anche una clausola REFERENCES che specifica un’azione a cascata. Tuttavia, un trigger AFTER su una tabella oggetto di un’azione a cascata può eseguire un’istruzione INSERT, UPDATE o DELETE su un’altra tabella o vista che attiva un trigger INSTEAD OF definito su quell’oggetto.

Compiti correlati

La seguente tabella elenca i compiti comuni associati ai vincoli di chiave primaria e chiave esterna.

Task	Topic
Descrive come creare una chiave primaria.	Creare chiavi primarie
Descrive come cancellare una chiave primaria.	Cancellare chiavi primarie
Descrive come modificare una chiave primaria.	Modificare le chiavi primarie
Descrive come creare relazioni a chiave esterna	Creare relazioni a chiave esterna
Descrive come modificare le relazioni a chiave esterna.	Modificare le relazioni a chiave esterna
Descrive come cancellare le relazioni a chiave esterna	Cancellare le relazioni a chiave esterna
Descrive come visualizzare le proprietà delle chiavi esterne.	Visualizza le proprietà delle chiavi esterne
Descrive come disabilitare i vincoli delle chiavi esterne per la replica.	Disabilita i vincoli delle chiavi esterne per la replica
Descrive come disabilitare i vincoli delle chiavi esterne durante una dichiarazione INSERT o UPDATE.	Disabilita i vincoli di chiave esterna con INSERT e UPDATE