Condividi tramite

  • Articolo

Vincoli di integrità referenziale di propagazione

I vincoli di integrità referenziale di propagazione consentono di definire le operazioni eseguite da SQL Server quando un utente tenta di eliminare o aggiornare una chiave alla quale fa riferimento una chiave esterna.

Le clausole REFERENCES delle istruzioni CREATE TABLE e ALTER TABLE supportano le clausole ON DELETE e ON UPDATE. Le operazioni di propagazione possono anche essere definite utilizzando la finestra di dialogo Relazioni chiavi esterne:

  • [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

  • [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

NO ACTION è il valore predefinito se non viene specificata ON DELETE o ON UPDATE.

  • ON DELETE NO ACTION
    Specifica che se si tenta di eliminare una riga contenente una chiave a cui fanno riferimento chiavi esterne in righe esistenti in altre tabelle, verrà generato un errore e verrà eseguito il rollback dell'istruzione DELETE.

  • ON UPDATE NO ACTION
    Specifica che se si tenta di aggiornare un valore di chiave in una riga e alla chiave fanno riferimento chiavi esterne in righe esistenti in altre tabelle, verrà generato un errore e verrà eseguito il rollback dell'istruzione UPDATE.

Con CASCADE, SET NULL e SET DEFAULT, le eliminazioni o gli aggiornamenti di valori di chiave possono influire sulle tabelle con relazioni di chiave esterna tracciabili alla tabella in cui è stata eseguita la modifica. Se le operazioni referenziali di propagazione sono state definite anche nelle tabelle di destinazione, tali operazioni saranno inoltre valide per le righe eliminate o aggiornate. Non è possibile specificare la clausola CASCADE per le chiavi esterne o primarie che includono una colonna timestamp.

  • ON DELETE CASCADE
    Specifica che se si tenta di eliminare una riga contenente una chiave a cui fanno riferimento chiavi esterne in righe esistenti in altre tabelle, verranno inoltre eliminate tutte le righe contenenti tali chiavi esterne.

  • ON UPDATE CASCADE
    Specifica che se si tenta di aggiornare un valore di chiave in una riga e a tale valore fanno riferimento chiavi esterne in righe esistenti in altre tabelle, tutti i valori che compongono la chiave esterna verranno anch'essi aggiornati al nuovo valore specificato per la chiave.

    Nota

    Non è possibile specificare la clausola CASCADE se nella chiave esterna o nella chiave a cui si fa riferimento è presente una colonna timestamp.

  • ON DELETE SET NULL
    Specifica che se si tenta di eliminare una riga contenente una chiave a cui fanno riferimento chiavi esterne in righe esistenti in altre tabelle, tutti i valori che compongono la chiave esterna presenti nelle righe a cui si fa riferimento verranno impostati su NULL. Per l'esecuzione di questo vincolo è necessario che le colonne chiave esterna ammettano i valori Null.

  • ON UPDATE SET NULL
    Specifica che se si tenta di aggiornare una riga contenente una chiave a cui fanno riferimento chiavi esterne in righe esistenti in altre tabelle, tutti i valori che compongono la chiave esterna presenti nelle righe a cui si fa riferimento verranno impostati su NULL. Per l'esecuzione di questo vincolo è necessario che le colonne chiave esterna ammettano i valori Null.

  • ON DELETE SET DEFAULT
    Specifica che se si tenta di eliminare una riga contenente una chiave a cui fanno riferimento chiavi esterne in righe esistenti in altre tabelle, tutti i valori che compongono la chiave esterna presenti nelle righe a cui si fa riferimento verranno impostati sul relativo valore predefinito. Per l'esecuzione di questo vincolo è necessario che per tutte le colonne chiave esterna della tabella di destinazione sia disponibile una definizione predefinita. Se una colonna ammette valori Null e non viene impostato un valore predefinito esplicito, NULL diventa il valore predefinito implicito della colonna. Per mantenere la validità del vincolo di chiave esterna, è necessario che per i valori non Null impostati da ON DELETE SET DEFAULT esistano valori corrispondenti nella tabella primaria.

  • ON UPDATE SET DEFAULT
    Specifica che se si tenta di aggiornare una riga contenente una chiave a cui fanno riferimento chiavi esterne in righe esistenti in altre tabelle, tutti i valori che compongono la chiave esterna presenti nelle righe a cui si fa riferimento verranno impostati sul relativo valore predefinito. Per l'esecuzione di questo vincolo è necessario che per tutte le colonne chiave esterna della tabella di destinazione sia disponibile una definizione predefinita. Se una colonna ammette valori Null e non viene impostato un valore predefinito esplicito, NULL diventa il valore predefinito implicito della colonna. Per mantenere la validità del vincolo di chiave esterna, è necessario che per i valori non Null impostati da ON UPDATE SET DEFAULT esistano valori corrispondenti nella tabella primaria.

Si consideri il vincolo FK_ProductVendor_Vendor_VendorID sulla tabella Purchasing.ProductVendor di AdventureWorks2008R2. Questo vincolo stabilisce una relazione di chiave esterna tra la colonna VendorID della tabella ProductVendor e la colonna chiave primaria VendorID della tabella Purchasing.Vendor. Se per il vincolo si specifica ON DELETE CASCADE e si elimina la riga in Vendor dove VendorID è uguale a 100, verranno inoltre eliminate le tre righe in ProductVendor dove VendorID è uguale a 100. Se per il vincolo si specifica ON UPDATE CASCADE e si aggiorna il valore VendorID nella tabella Vendor modificandolo da 100 a 155, verranno inoltre aggiornati i valori VendorID nelle tre righe di ProductVendor dove i valori correnti di VendorID sono uguali a 100.

Non è possibile specificare ON DELETE CASCADE per una tabella in cui è presente un trigger INSTEAD OF DELETE. Per queste tabelle non è possibile specificare ON DELETE SET NULL, ON DELETE SET DEFAULT, ON UPDATE CASCADE, ON UPDATE SET NULL e ON UPDATE SET DEFAULT.

Operazioni di propagazione multiple

Singole istruzioni DELETE o UPDATE possono avviare una serie di operazioni referenziali di propagazione. Ad esempio, un database contiene le tre tabelle TabellaA, TabellaB e TabellaC. Una chiave esterna in TabellaB è definita con ON DELETE CASCADE nella chiave primaria in TabellaA. Una chiave esterna in TabellaC è definita con ON DELETE CASCADE nella chiave primaria in TabellaB. Se un'istruzione DELETE elimina righe in TabellaA, verranno inoltre eliminate le righe in TabellaB che contengono chiavi esterne corrispondenti alle chiavi primarie eliminate in TabellaA e quindi verranno eliminate le righe in TabellaC che contengono chiavi esterne corrispondenti alle chiavi primarie eliminate in TabellaB.

Le serie di operazioni referenziali di propagazione attivate da una singola istruzione DELETE o UPDATE devono formare un albero che non contiene alcun riferimento circolare. Nessuna tabella può essere presente più volte nell'elenco di tutte le operazioni referenziali di propagazione generate dall'istruzione DELETE o UPDATE. Inoltre, nell'albero delle operazioni non devono essere presenti più percorsi per una tabella specificata. Ogni ramo dell'albero si interrompe se viene rilevata una tabella per la quale è stata specificata l'istruzione NO ACTION oppure se tale istruzione rappresenta il valore predefinito.

Trigger e operazioni referenziali di propagazione

Le operazioni referenziali di propagazione attivano i trigger AFTER UPDATE o AFTER DELETE nel modo seguente:

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 tramite 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 che vengono elaborate per un'operazione UPDATE o DELETE alla volta dopo che tutti i trigger di tutte le catene primarie sono stati attivati. È possibile ripetere il processo in modo ricorsivo per le operazioni UPDATE o DELETE successive.

L'esecuzione di operazioni CREATE, ALTER, DELETE o di altre operazioni DDL (Data Definition Language) nei trigger può comportare l'attivazione dei trigger DDL e la conseguente esecuzione di operazioni DELETE o UPDATE che avviano catene e trigger di propagazione aggiuntivi.

Se in una catena di operazioni referenziali di propagazione si verifica un errore, verrà generato un errore, non verrà attivato alcun trigger AFTER nella catena specifica e verrà 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.

Informazioni del catalogo relative ai vincoli referenziali di propagazione

L'esecuzione di query nella vista del catalogo sys.foreign_keys restituisce i valori seguenti che indicano il vincolo referenziale di propagazione specificato per una chiave esterna.

Valore

Descrizione

0

NO ACTION

1

CASCADE

2

SET NULL

3

SET DEFAULT

Le colonne UPDATE_RULE e DELETE_RULE restituite da sp_fkeys e sp_foreignkeys restituiscono 0 se viene specificato CASCADE, SET NULL o SET DEFAULT e restituiscono 1 se viene specificato NO ACTION o se NO ACTION rappresenta il valore predefinito.

Se l'oggetto specificato per sp_help è una chiave esterna, il set dei risultati di output contiene le colonne seguenti.

Nome colonna

Tipo di dati

Descrizione

delete_action

nvarchar(9)

Indica se l'operazione di eliminazione è CASCADE, SET NULL, SET DEFAULT, NO ACTION o N/A (non applicabile).

update_action

nvarchar(9)

Indica se l'operazione di aggiornamento è CASCADE, SET NULL, SET DEFAULT, NO ACTION o N/A (non applicabile).