Vincoli di chiave primaria e chiave esterna

Si applica a: SQL Server 2016 (13.x) e versioni successive database SQL di Azure AzureSQL Managed InstanceSQL database in Microsoft Fabric

Le chiavi primarie e le chiavi esterne sono due tipi di vincoli che è possibile usare per applicare l'integrità dei dati nelle tabelle di SQL Server. Si tratta di importanti oggetti di database.

Vincoli della chiave primaria

Una tabella include in genere una colonna o una combinazione di colonne i cui valori identificano in modo univoco ogni riga della tabella. Questa colonna, o queste colonne, è detta chiave primaria (PK) della tabella e garantisce l'integrità dell'entità della tabella. Poiché i vincoli di chiave primaria garantiscono l'univocità dei dati, vengono spesso definiti su una colonna identity.

Quando si specifica un vincolo di chiave primaria per una tabella, il motore di database assicura l'univocità dei dati creando automaticamente un indice univoco per le colonne chiave primaria. Questo indice consente inoltre di accedere rapidamente ai dati quando si utilizza la chiave primaria 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 ciascuna combinazione di valori di tutte le colonne nella definizione del vincolo di chiave primaria deve essere univoca.

Le colonne ProductID e VendorID nella tabella Purchasing.ProductVendor costituiscono un vincolo di chiave primaria composto per la tabella, come illustrato nella figura seguente. Ciò assicura che ogni riga nella tabella ProductVendor contenga una combinazione univoca di ProductID e VendorID. e impedisce l'inserimento di righe duplicate.

Diagramma delle righe in una tabella per un vincolo di chiave primaria composita.

  • Una tabella può includere un solo vincolo di chiave primaria.
  • Una chiave primaria non può superare 32 colonne e una lunghezza totale di 900 byte.
  • Se l'indice viene generato da un vincolo di chiave primaria, nella tabella sarà possibile creare non più di 999 indici non cluster e di 1 indice cluster.
  • Se per un vincolo di chiave primaria non viene specificato CLUSTERED o NONCLUSTERED, viene usato CLUSTERED se nella tabella non è presente alcun indice clustered.
  • Tutte le colonne specificate in un vincolo di chiave primaria devono essere definite come NOT NULL. Se la nullabilità non è specificata, per tutte le colonne che partecipano a un vincolo di chiave primaria viene impostata l'opzione NOT NULL.
  • Se si definisce una chiave primaria in una colonna di tipo CLR definito dall'utente, è necessario che l'implementazione del tipo supporti l'ordinamento binario.

Vincoli di chiave esterna

Per chiave esterna si intende una colonna o combinazione di colonne utilizzata per stabilire e applicare un collegamento tra i dati di due tabelle per controllare i dati che possono essere archiviati nella tabella della chiave esterna. In un riferimento a una chiave esterna viene creato un collegamento tra tabelle quando le colonne contenenti il valore della chiave primaria per una tabella vengono utilizzate come riferimento dalle colonne di un'altra tabella. Questa colonna diventa una chiave esterna nella seconda tabella.

Nella tabella Sales.SalesOrderHeader, ad esempio, è incluso un collegamento di chiave esterna alla tabella Sales.SalesPerson, in quanto esiste una relazione logica tra gli ordini di vendita e i 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, non è possibile inserire un valore per SalesPersonID 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). In SQL Server 2016 (13.x) il limite per il numero di altre tabelle e colonne che possono fare riferimento alle colonne in una singola tabella (riferimenti in ingresso) è stato aumentato da 253 a 10.000. (richiede almeno il livello di compatibilità 130). All'incremento vengono applicate le seguenti restrizioni:

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

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

  • Più di 253 riferimenti a chiavi esterne non sono attualmente supportati per gli indici columnstore, le tabelle ottimizzate per la memoria, Stretch Database o le tabelle con chiavi esterne partizionate.

    Important

    Stretch Database è deprecato in SQL Server 2022 (16.x) e database SQL di Azure. Questa funzionalità verrà rimossa nelle versioni future del motore di database. Evitare di usare questa funzionalità in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni in cui è attualmente implementata.

Indici sui vincoli di chiave esterna

A differenza di quanto avviene per i vincoli di chiave primaria, la creazione di un vincolo di chiave esterna non determina automaticamente la creazione di un indice corrispondente. La creazione manuale di un indice in una chiave esterna, tuttavia, rappresenta spesso un'operazione utile per i motivi seguenti:

  • Le colonne chiave esterna vengono in genere utilizzate nei criteri di join quando i dati provenienti da tabelle correlate vengono riuniti in query confrontando le colonne nel vincolo di chiave esterna di una tabella con le colonne chiave primaria o univoca nell'altra tabella. Un indice consente al motore di database di trovare rapidamente i dati correlati nella tabella della chiave esterna. La creazione di questo indice, tuttavia, non è un requisito necessario. È possibile riunire i dati di due tabelle correlate anche se non è stato definito alcun vincolo 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 riunite in una query che utilizza le chiavi come criteri.

  • Le modifiche apportate ai vincoli di chiave primaria vengono confrontate con i vincoli di chiave esterna nelle tabelle correlate.

Integrità referenziale

Benché lo scopo primario di un vincolo di chiave esterna consista nel controllare i dati che è possibile archiviare nella tabella della chiave esterna, tale vincolo controlla inoltre le modifiche apportate ai dati nella tabella della chiave primaria. Se, ad esempio, la riga relativa a un venditore viene eliminata dalla tabella Sales.SalesPerson e l'ID del venditore viene usato per gli ordini di vendita inclusi nella tabella Sales.SalesOrderHeader, viene interrotta l'integrità relazionale tra le due tabelle. Gli ordini di vendita del venditore eliminato risultano isolati (orfani) nella tabella SalesOrderHeader e privi di un collegamento ai dati inclusi nella tabella SalesPerson.

Un vincolo di chiave esterna impedisce questa situazione. Il vincolo garantisce l'integrità referenziale assicurando che non sia possibile apportare modifiche ai dati nella tabella della chiave primaria se tali modifiche invalidano il collegamento ai dati nella tabella con chiave esterna. Se si tenta di eliminare la riga in una tabella della chiave primaria oppure di modificare un valore della chiave primaria, l'azione ha esito negativo se il valore della chiave primaria eliminato o modificato corrisponde a un valore nel vincolo di chiave esterna di un'altra tabella. Per modificare o eliminare correttamente una riga in un vincolo di chiave esterna, è necessario innanzitutto modificare o eliminare i dati della chiave esterna nella tabella della chiave esterna e collegare quindi la chiave esterna ad altri dati della chiave primaria.

Integrità referenziale a catena

I vincoli di integrità referenziale di propagazione consentono di definire le operazioni eseguite dal motore di database quando un utente tenta di eliminare o aggiornare una chiave alla quale fa riferimento una chiave esterna. È possibile definire le azioni di propagazione seguenti.

  • NO ACTION

    Il motore di database genera un errore e l'azione di eliminazione o aggiornamento della riga nella tabella padre viene annullata.

  • CASCADE

    Le righe corrispondenti vengono aggiornate o eliminate nella tabella di riferimento quando la riga viene aggiornata o eliminata nella tabella padre. Non è possibile specificare la clausola CASCADE se nella chiave esterna o nella chiave di riferimento è presente una colonna data e ora. Non è possibile specificare ON DELETE CASCADE per una tabella con un trigger INSTEAD OF DELETE. Non è possibile specificare ON UPDATE CASCADE per tabelle in cui sono presenti trigger INSTEAD OF UPDATE.

  • SET NULL

    Tutti i valori che costituiscono la chiave esterna vengono impostati su NULL quando viene aggiornata o eliminata la riga corrispondente nella tabella padre. Affinché questo vincolo venga applicato, le colonne della chiave esterna devono poter contenere valori null. Non può essere specificato per le tabelle in cui sono presenti trigger INSTEAD OF UPDATE.

  • SET DEFAULT

    Tutti i valori che compongono la chiave esterna vengono impostati sui rispettivi valori predefiniti se viene eliminata o aggiornata la riga corrispondente nella tabella padre. Per l'esecuzione di questo vincolo, è necessario che per tutte le colonne chiave esterna siano definiti valori predefiniti. Se una colonna ammette i valori Null e non viene impostato un valore predefinito esplicito, NULL diventa il valore predefinito implicito della colonna. Non può essere specificato per le tabelle in cui sono presenti trigger INSTEAD OF UPDATE.

È possibile combinare le azioni CASCADE, SET NULL, SET DEFAULT e NO ACTION in tabelle con relazioni referenziali reciproche. Se il motore di database rileva l'azione NO ACTION, l'operazione viene arrestata e viene eseguito il rollback delle azioni CASCADE, SET NULL e SET DEFAULT correlate. Quando un'istruzione DELETE genera una combinazione di azioni CASCADE, SET NULL, SET DEFAULT o NO ACTION, tutte le azioni CASCADE, SET NULL e SET DEFAULT vengono applicate prima che il motore di database controlli la presenza di eventuali NO ACTION.

Trigger e azioni referenziali a catena

Le azioni referenziali a catena attivano i trigger AFTER UPDATE o AFTER DELETE come segue:

  • Vengono eseguite per prime tutte le operazioni referenziali di propagazione determinate direttamente dall'istruzione DELETE o UPDATE originale.

  • Se nelle tabelle interessate sono stati definiti trigger AFTER, tali trigger vengono attivati dopo che sono state eseguite tutte le operazioni di propagazione. L'ordine di attivazione dei trigger è inverso rispetto all'ordine delle operazioni di propagazione. Se in una tabella sono presenti più trigger, tali trigger vengono attivati in ordine casuale a meno che per la tabella non sia stato specificato un primo o un ultimo trigger dedicato. L'ordine viene specificato usando sp_settriggerorder.

  • Se dalla tabella che rappresenta la destinazione diretta di un'azione UPDATE o DELETE vengono originate più catene di propagazione, l'ordine di attivazione dei rispettivi trigger non è specificato. Prima che una catena inizi ad attivare i propri trigger è tuttavia necessario che un'altra catena abbia completato l'attivazione di tutti i relativi trigger.

  • Un trigger AFTER presente nella tabella di destinazione diretta di un'azione UPDATE o DELETE viene attivato indipendentemente dal fatto che influisca o meno su qualsiasi riga. In questo caso, la propagazione non avrà effetti su nessun'altra tabella.

  • Se uno dei trigger precedenti esegue operazioni UPDATE o DELETE su altre tabelle, tali operazioni possono avviare catene di propagazione secondarie Queste catene secondarie vengono elaborate una alla volta per ogni operazione UPDATE o DELETE, dopo l'attivazione di tutti i trigger in tutte le catene primarie. È possibile ripetere il processo in modo ricorsivo per le operazioni UPDATE o DELETE successive.

  • L'esecuzione di CREATE, ALTER, DELETE o di altre istruzioni del linguaggio di definizione dei dati (DDL) all'interno dei trigger può causare l'attivazione dei trigger DDL. Ciò potrebbe successivamente eseguire operazioni DELETE o UPDATE che avviano ulteriori catene ed eventi attivatori a cascata.

  • Se viene generato un errore all'interno di una specifica catena di azioni referenziali a catena, viene segnalato un errore, in tale catena non viene attivato alcun trigger AFTER e viene eseguito il rollback dell'operazione DELETE o UPDATE che ha creato la catena.

  • Una tabella in cui è presente un trigger INSTEAD OF non può inoltre includere un clausola REFERENCES che specifica un'operazione di propagazione. Un trigger AFTER in una tabella di destinazione di un'operazione di propagazione può tuttavia eseguire un'istruzione INSERT, UPDATE o DELETE su un'altra tabella o vista che attiva un trigger INSTEAD OF definito nell'oggetto specifico.

Contenuti correlati

  • Last updated on