Il concetto di integrità applicato ad un database si traduce nel tenere sotto controllo la consistenza e la precisione dei dati. Bisogna assicurarsi che un campo possa accettare un determinato insieme di valori (Integrità di dominio), che ogni tabella abbia un insieme di campi che fungano da indentificatore univoco di ogni riga (Integrità di entità) e che le relazioni definite tra le tabelle non possano essere violate (Integrità referenziale).
Questo articolo tratta l'implementazione fisica in SQL Server 2005 dell'integrità dei dati in un database OLTP concentrandosi su quella referenziale.
Integrità referenziale
L'integrità referenziale coinvolge due tabelle: una "referenced table" (tabella referenziata) che contiene la chiave primaria (PRIMARY KEY o PK) e una "referencing table" (tabella referenziante) che si lega ad essa attraverso una chiave esterna (FOREIGN KEY o FK).
Nota: È utile ricordare che la PRIMARY KEY di una tabella è l'insieme di colonne in cui valori, presi insieme, sono univoci nell'ambito della tabella. Perciò, si suol dire che la PK è in grado di identificare univocamente una riga di tabella.
Oltre che a una PK, la FK può far riferimento ad una colonna (o un insieme di colonne) su cui è definito un vincolo di unicità (UNIQUE CONSTRAINT). In ogni caso occorre che il tipo di dato delle colonne in relazione sia identico (salvo l'attributo di NULL/NOT NULL).
Tale legame può essere più o meno "forte". Ad esempio si può prevedere un vincolo di partecipazione tra due tabelle tale che, cancellando una record identificato dalla relativa PK sulla tabella referenziata, vengano cancellati tutti i record, sulla tabella referenziante, che fanno riferimento quella chiave primaria.
In ogni caso, la relazione condizionerà la tabella referenziante poiché in essa non sarà possibile inserire o aggiornare il valore di una chiave esterna se tale valore non esiste in un'istanza della PK della tabella referenziata.
Lo standard ANSI SQL-92 parla di azioni referenziali al verificarsi di aggiornamenti della tabella referenziata e ne definisce quattro:
- NO ACTION che impedisce le modifiche alla PK referenziata da una FK;
- SET NULL consente la cancellazione e la modifica e produce un aggiornamento del valore della FK al valore
NULL; - SET DEFAULT imposta il valore della FK al suo valore di
DEFAULT; - CASCADE consente la cancellazione e la modifica della PK, producendo, nel primo caso, la cancellazione di tutte le istanze in relazione della tabella referenziante, e nel secondo caso l'aggiornamento del valore della FK.
SQL Server (sin dalla versione 6.0) permette l'implementazione dell'integrità referenziale in due modi:
- dichiarativo attraverso la definizione di un oggetto di tipo FOREIGN KEY;
- procedurale mediante trigger o stored procedure.
Microsoft suggerisce l'adozione dell'integrità dichiarativa ed invita a utilizzare quella procedurale per risolvere business logic più complesse. Se si utilizza SQL Server 2000 va considerato che essa supporta in modo dichiarativo solo due delle quattro azioni referenziali definite dallo standard SQL-92: NO ACTION e CASCADE. SQL Server 2005 colma questa lacuna rendendo possibile tutte e quattro le azioni referenziali. In ogni caso, nulla vieta, di implementare l'integrità referenziale attraverso i trigger.
Dichiarare un vincolo di FOREIGN KEY rappresenta sicuramente il modo più semplice di agire sulle azioni referenziali, ma è bene tener presente alcune limitazioni e inconvenienti in cui ci si potrebbe imbattere.
Innanzitutto, non è possibile usare contemporaneamente sulla stessa relazione sia l'integrità dichiarativa che i trigger, poiché nel momento della violazione del vincolo dichiarativo, l'istruzione non viene eseguita ed il trigger non parte.
Dal punto di vista delle performance, un uso eccessivo di FOREIGN KEY può causare un degrado delle prestazioni, quindi è opportuno considerare la creazione di un indice sulla FK per consentire operazioni di join più efficienti. È bene ricordare anche che i comandi di caricamento di massa bcp o BULK INSERT non prevedono, di default, il controllo dei vincoli referenziali -proprio allo scopo di rendere tali operazioni più veloci. Quello che segue è un esempio di dichiarazione di un vincolo che prevede il NO ACTION a seguito della cancellazione e il CASCADE a seguito dell'aggiornamento:
ALTER TABLE [titleauthor]
ADD CONSTRAINT [FK_titleauthor_author] FOREIGN KEY ([au_id]) REFERENCES authors ([au_id])
ON DELETE NO ACTION
ON UPDATE CASCADE
Nel seguito dell'articolo vediamo come implementare le due nuove azioni SET DEFAULT e SET NULL in modalità dichiarativa.
In un database denominato [ReferentialActions], creo due tabelle [Product] e [ProductCategory] legate da un vincolo referenziale dalla chiave esterna definita sul campo [Product].[CategoryID] verso la chiave primaria [ProductCategory].[CategoryID], che, a seguito di UPDATE del valore della chiave primaria, aggiorna la chiave esterna (ON CASCADE) con lo stesso valore, ed a seguito di DELETE di una riga della tabella [ProductCategory], aggiorna la chiave esterna col valore NULL:
CREATE DATABASE [ReferentialActions]
GO
USE [ReferentialActions]
GO
CREATE TABLE [Product](
[ProductID] [int] NOT NULL PRIMARY KEY,
[ProductName] [nvarchar](50) NOT NULL,
[CategoryID] [int] NULL,
) ON [PRIMARY]
GO
CREATE TABLE [ProductCategory](
[CategoryID] [int] NOT NULL PRIMARY KEY,
[CategoryName] [nvarchar](30) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [Product]
ADD
CONSTRAINT [FK_Product_ProductCategory] FOREIGN KEY([CategoryID])
REFERENCES [ProductCategory] ([CategoryID])
ON UPDATE CASCADE
ON DELETE SET NULL
GO
Innanzitutto popoliamo le due tabelle rispettando il vincolo referenziale già definito:
INSERT INTO [ProductCategory] ([CategoryID], [CategoryName]) VALUES (1, 'Books')
INSERT INTO [ProductCategory] ([CategoryID], [CategoryName]) VALUES (2, 'Software')
INSERT INTO [Product] ([ProductID], [ProductName], [CategoryID]) VALUES ( 10, 'Inside Sql Server 2000', 1)
INSERT INTO [Product] ([ProductID], [ProductName], [CategoryID]) VALUES ( 11, 'Asp.net 2.0 Guida Per Lo Sviluppatore', 1)
INSERT INTO [Product] ([ProductID], [ProductName], [CategoryID]) VALUES ( 20, 'Visual Studio 2005 Professional', 2)
INSERT INTO [Product] ([ProductID], [ProductName], [CategoryID]) VALUES ( 21, 'OpenOffice', 2)
così da avere le seguenti istanze di tabella:
ProductCategory CategoryID |CategoryName ------------------------ 1 |Books 2 |Software Product ProductID |ProductName |CategoryID -------------------------------------------------------- 10 |Inside Sql Server 2000 | 1 11 |Asp.net 2.0 Guida Per Lo Sviluppatore| 1 20 |Visual Studio 2005 Professional | 2 21 |OpenOffice | 2 Proviamo il comportamento dell'azione referenziale SET NULL definita in fase di DELETE, lanciando una cancellazione di una riga di [ProductCategory] che produce un aggiornamento al valore NULL del campo [CategoryID] di tutte le righe referenziate in [Product]: DELETE FROM [ProductCategory] WHERE [CategoryID] = 2 SELECT * FROM [Product] (results) ProductID |ProductName |CategoryID ----------------------------------------------------------- 10 |Inside Sql Server 2000 | 1 11 |Asp.net 2.0 Guida Per Lo Sviluppatore| 1 20 |Visual Studio 2005 Professional | NULL 21 |OpenOffice | NULL
Per provare l'azione referenziale SET DEFAULT aggiungiamo un vincolo di DEFAULT sul campo [Product].[CategoryID]:
ALTER TABLE [Product]
ADD
CONSTRAINT [DEFAULT_Product_Category]
DEFAULT 0 FOR [CategoryID]
GO
quindi modifichiamo la definizione del vincolo referenziale
ALTER TABLE [Product]
DROP CONSTRAINT [FK_Product_ProductCategory]
GO
ALTER TABLE [Product]
ADD
CONSTRAINT [FK_Product_ProductCategory] FOREIGN KEY([CategoryID])
REFERENCES [ProductCategory] ([CategoryID])
ON UPDATE CASCADE
ON DELETE SET DEFAULT
GO
Non ci resta che lanciare una cancellazione di almeno una riga di [ProductCategory] verificando il risultato atteso, ovvero l'aggiornamento al valore di default del campo [Product].[CategoryID] (pari a zero) dove quest'ultimo è uguale al [ProductCategory] eliminato:
DELETE FROM [ProductCategory] WHERE [CategoryID] = 1 SELECT * FROM [Product] GO (results) Messaggio 547, livello 16, stato 0, riga 1 L'istruzione DELETE è in conflitto con il vincolo FOREIGN KEY "FK_Product_ProductCategory". Il conflitto si è verificato nella tabella "dbo.ProductCategory", column 'CategoryID' del database "ReferentialActions". L'istruzione è stata interrotta. ProductID |ProductName |CategoryID ----------------------------------------------------------- 10 |Inside Sql Server 2000 | 1 11 |Asp.net 2.0 Guida Per Lo Sviluppatore| 1 20 |Visual Studio 2005 Professional | NULL 21 |OpenOffice | NULL
Le cose non sono andate esattamente come volevamo. Il messagio d'errore restituito, però, è molto chiaro sul perchè di questo imprevisto: nel tentativo di impostare a 0 (zero) il campo [Product].[CategoryID] è stato violato il vincolo di integrità referenziale poichè non esiste alcuna riga di [ProductCategory] che abbia un valore [CategoryID]=0. Dunque, inseriamola:
INSERT INTO [ProductCategory] ([CategoryID], [CategoryName]) VALUES (0, 'None')
GO
Se riproviamo a cancellare la riga di [ProductCategory] con [CategoryID] = 1:
DELETE FROM [ProductCategory] WHERE [CategoryID] = 1 SELECT * FROM [Product] otteniamo il risultato atteso: (results) ProductID |ProductName |CategoryID ----------------------------------------------------------- 10 |Inside Sql Server 2000 | 0 11 |Asp.net 2.0 Guida Per Lo Sviluppatore| 0 20 |Visual Studio 2005 Professional | NULL 21 |OpenOffice | NULL
Per concludere, considerato che questa riga di [ProductCategory] è di vitale importanza per la nostra integrità referenziale, dovremmo assicurarci che essa sia sempre presente. In realtà, non c'è bisogno che ci impegniamo più di tanto in questo compito poichè viene egregemente assolto dalla stessa integrità referenziale. Infatti, tentare di cancella la riga di [ProductCategory] dove [CategoryID] = 0, produce - per effetto dell'azione SET DEFAULT - l'aggiornamento di [Product].[CategoryID] al valore 0 (zero) che non troverebbe più corrispondenza con [ProductCategory].[CategoryID] che si sta cancellando. Per cui, Sql Server 2005 restituirebbe il seguente messaggio di errore:
Messaggio 547, livello 16, stato 0, riga 1 L'istruzione DELETE è in conflitto con il vincolo FOREIGN KEY "FK_Product_ProductCategory". Il conflitto si è verificato nella tabella "dbo.ProductCategory", column 'CategoryID' del database "ReferentialActions". L'istruzione è stata interrotta.
Usare i trigger
Rispetto all'uso dei vincoli dichiarativi, i trigger consentono al DBA SQL Server alcuni piccoli vantaggi a fronte di un costo di produzione e manutenzione più elevato: danno la possibilità di visualizzare messaggi di errore personalizzati per rendere più esplicativi quelli generici inviati dopo la violazione delle relazioni dichiarative; consentono l'implementazione delle quattro azioni referenziali descritte dallo standard SQL-92 (inclusi
CREATE TRIGGER tr_set_default_authors CREATE TRIGGER tr_set_null_authors Si noti l'uso della variabile di sistema Usando i trigger per implementare le azioni di MS-SQL Server 2005 ha definitivamente allineato la sua implementazione dell'integrità referenziale allo standard ANSI SQL-92, lasciando, ormai, da parte l'utilizzo dei più laboriosi trigger per questo delicato compito. Questi ultimi continuano a rappresentare una carta in più da giocare per fronteggiare logiche di dati più complesse nonché per manutenere database MS-SQL Server sviluppati con versioni precedenti alla 6.0, in cui, per forza di cose, bisognava essere dei discreti programmatori T-SQL per scrivere i trigger necessari a mantenere integri i dati.SET NULL e SET DEFAULT) anche con le versioni precedenti a SQL Server 2005; permettono il CASCADE su una tabella che è auto-referenziata. Lo script che seghe mostra due esempi che implementano (il primo) il SET NULL seguito dell'aggiornamento della chiave primaria della tabella referenziata e , (il secondo) il SET DEFAULT
ON authors FOR UPDATE
AS
DECLARE @rows_effected int
BEGIN
SET @rows_effected = @@ROWCOUNT
IF @rows_effected = 0
RETURN
IF UPDATE(au_id)
IF @rows_effected = 1
UPDATE titleauthor
SET au_id = DEFAULT
FROM titleauthor
INNER JOIN Deleted ON titleauthor.au_id = Deleted.au_id
INNER JOIN Inserted ON Inserted.au_id <> Deleted.au_id
ELSE
BEGIN
RAISERROR ('Si è cercato di aggiornare la chiave primaria di %d righe',16,1,@rows_effected)
ROLLBACK TRANSACTION
END
END
GO
ON authors FOR DELETE
AS
DECLARE @rows_effected int
BEGIN
IF @@ROWCOUNT=0
RETURN
UPDATE titleauthor
SET au_id = NULL
WHERE au_id IN
(SELECT DISTINCT au_id FROM Deleted)
END
GO
@@ROWCOUNT. Essa contiene il numero di righe coinvolte nell'ultima istruzione ed è opportuno valutarla poiché il trigger scatta anche se l'operazione sulla tabella non influenza nessuna riga. In questo modo è possibile controllare le situazioni in cui un'istruzione non sortisce l'effetto desiderato e, per esempio, lanciare un messaggio personalizzato dall'interno del trigger. Inoltre, si noti che, qualora il valore di @@ROWCOUNT debba essere valutato in più volte all'interno del trigger, esso è assegnato ad una variabile dal momento che viene impostato a 0 (zero) da qualsiasi altra istruzione. SET NULL o SET DEFAULT, occorre necessariamente utilizzare i trigger per le azioni di NO ACTION o di CASCADE, a causa dell'incompatibilità fra trigger e integrità dichiarativa. Per impedire la violazione della relazione bisognerà creare un trigger sulla tabella referenziante che gestirà l'INSERT e l'UPDATE verificando la presenza di un'istanza correlata nella tabella referenziante. Per il CASCADE, invece, occorrerà creare sulla tabella referenziata un trigger per gestire l'UPDATE e uno per la DELETE.Conclusioni
