Integrità referenziale con SQL Server
Posted
giovedì 30 dicembre 2004 15.48
by
lorenzo
Mi è successo di incappare in un errore riportato da SQL Server che - a mio
modesto parere - se non è un buco, è quantomeno poco chiaro e rischia di
trarre in inganno il DBA.
L'errore si presenta nelle situazioni in cui si definiscono diverse relazioni di
integrità referenziale residenti sulla stessa tabella ed aventi come
"padre" una seconda tabella.
I books on line parlano di una situazione analoga a quella che vado a descrivere
solo nel caso di relazioni autoreferenzianti. In quel caso è vero che la
situazione può prendere strade che portano ad un loop, ma in questo caso no!
Ma vediamo di capire bene di cosa stò parlando con un esempio...
Supponiamo di avere due tabelle (Ordini ed Utenti) aventi la struttura
riportata di seguito:
use [Test_DB]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Ordini]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Ordini]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Utenti]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Utenti]
GO
CREATE TABLE [dbo].[Ordini] (
[OrdineID] [int] NOT NULL ,
[Data] [datetime] NULL ,
[UtenteInserimento] [int] NULL ,
[UtenteAggiornamento] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Utenti] (
[UtenteID] [int] NOT NULL ,
[Cognome] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Nome] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
|
Una volta definite le tabelle, passiamo ad inserire le
loro chiavi primarie.
ALTER TABLE [dbo].[Ordini] WITH NOCHECK ADD
CONSTRAINT [PK_Ordini] PRIMARY KEY CLUSTERED
(
[OrdineID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Utenti] WITH NOCHECK ADD
CONSTRAINT [PK_Utenti] PRIMARY KEY CLUSTERED
(
[UtenteID]
) ON [PRIMARY]
GO
|
A questo punto, diciamo che vogliamo porre qualche
vincolo allo scopo di garantire l'integrità dei dati presenti nelle due
tabelle. La cosa più semplice e lineare sarebbe quella di inserire due foreign
key per i campi UtenteInserimento ed UtenteAggiornamento vincolandoli al campo
UtenteID della relativa tabella.
Se però vogliamo dare ai nostri utenti la possibilità di modificare il
codice identificativo degli utenti anche dopo che essi hanno inserito e/o
aggiornato degli ordini, la definizione delle relazioni dovrà essere come
riportato di seguito:
ALTER TABLE [dbo].[Ordini] ADD
CONSTRAINT [FK_Ordini_Utenti_Inserimento] FOREIGN KEY
(
[UtenteInserimento]
) REFERENCES [dbo].[Utenti] (
[UtenteID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[Ordini] ADD
CONSTRAINT [FK_Ordini_Utenti_Aggiornamento] FOREIGN KEY
(
[UtenteAggiornamento]
) REFERENCES [dbo].[Utenti] (
[UtenteID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
|
Specificando le clausole ON DELETE e ON UPDATE, infatti,
si ottiene la propagazione automatica su tutti i record referenziati delle
eventuali modifiche e cancellazioni svolte sulla tabella Utenti.
Se, però, si esegue quest'ultima parte dello script si
ottiene il seguente errore:
Server: messaggio 1785, livello 16, stato 1, riga 1
L'impostazione del vincolo FOREIGN KEY 'FK_Ordini_Utenti_Aggiornamento' nella tabella 'Ordini' potrebbe generare cicli o percorsi a catena multipla. Specificare ON DELETE NO ACTION oppure ON UPDATE NO ACTION oppure modificare altri vincoli FOREIGN KEY.
Server: messaggio 1750, livello 16, stato 1, riga 1
Impossibile creare il vincolo. Vedere gli errori precedenti.
Analizzando la struttura (molto semplice, per la verità) delle due tabelle,
è evidente che non c'è alcun rischio di ciclo o di percorso multiplo come
invece ci segnala l'RDBMS di Microsoft.
Soluzione
Non esiste una soluzione "pulita": SQL Server 2000 non supporta questo
tipo di integrità referenziale "multipla". Faccio notare, però, che
RDBMS come Oracle o addirittura Access la supportano tranquillamente...
L'unica scappatoia che si presenta è quella di implmentare l'integrità
referenziale non con le relazioni bensì con i trigger.
Lorenzo Braidi
...acquista "Database Design",
il mio primo libro!