-- Drop table dbo.Magazzini if exists
IF (OBJECT_ID('Magazzini', 'U') IS NOT NULL)
DROP TABLE dbo.Magazzini
GO
-- Create table dbo.Magazzini
CREATE TABLE [dbo].[Magazzini](ID INT IDENTITY(1, 1) NOT NULL, CODICE CHAR(4) NOT NULL, DESCRIZIONE CHAR(80) NOT NULL PRIMARY KEY (ID))
dbo.Vani:
-- Drop table dbo.Vani if exists
IF (OBJECT_ID('Vani', 'U') IS NOT NULL)
DROP TABLE dbo.VaniGO
-- Create table dbo.Vani
CREATE TABLE [dbo].[Vani](ID INT IDENTITY(1, 1) NOT NULL, CODICE CHAR(6) NOT NULL, DESCRIZIONE CHAR(80) NOT NULL, IDMAGAZZINI INT NOT NULL PRIMARY KEY (ID))
-- Foreign Key
ALTER TABLE [dbo].[Vani] WITH NOCHECK ADD CONSTRAINT [FK__Magazzini_ID] FOREIGN KEY (IDMAGAZZINI) REFERENCES [dbo].[Magazzini] (ID)
Le due tabelle anagrafiche contengono i seguenti dati di prova:
-- Inserimento dati di prova
INSERT INTO [dbo].[Magazzini] (CODICE, DESCRIZIONE) VALUES ('MATP', 'Materie Prime')
INSERT INTO [dbo].[Vani] (CODICE, DESCRIZIONE, IDMAGAZZINI) Select 'VTERRA', 'Vano a Terra', (Select Id From Magazzini Where Codice='MATP')
INSERT INTO [dbo].[Vani] (CODICE, DESCRIZIONE, IDMAGAZZINI) Select 'CAS001', 'Cassetto 001', (Select Id From Magazzini Where Codice='MATP')
INSERT INTO [dbo].[Vani] (CODICE, DESCRIZIONE, IDMAGAZZINI) Select 'CAS002', 'Cassetto 002', (Select Id From Magazzini Where Codice='MATP')
INSERT INTO [dbo].[Magazzini] (CODICE, DESCRIZIONE) VALUES ('SMLA', 'Semilavorati')
INSERT INTO [dbo].[Vani] (CODICE, DESCRIZIONE, IDMAGAZZINI) Select 'VTERRA', 'Vano a Terra', (Select Id From Magazzini Where Codice='SMLA')
Ipotizziamo di dover implementare un vincolo che garantisca l’univocità del campo codice vano impedendo la creazione di due vani con lo stesso codice (a prescindere dal magazzino di appartenenza).
Il vincolo dovrà però tenere conto dei vani a terra (o vani di default) che vogliamo mantenere codificati con un codice fisso per tutti i vani a terra di tutti i magazzini. Non è quindi possibile implementare un constraint di tipo UNIQUE perché impedirebbe la creazione di nuovi magazzini e dei relativi vani a terra.
Scegliamo quindi d’implementare un CHECK constraint che sfrutta la funzione dbo.UDF_UNIQUE_COD_VANO() per verificare i valori che possono essere memorizzati nella colonna codice. La funzione dbo.UDF_UNIQUE_COD_VANO() ed il vincolo CHECK sono definiti in questo modo:
-- Drop function if exists
IF (OBJECT_ID('UDF_UNIQUE_COD_VANO', 'FN') IS NOT NULL)
DROP FUNCTION dbo.UDF_UNIQUE_COD_VANO
GO
-- Create function
CREATE FUNCTION dbo.UDF_UNIQUE_COD_VANO(@CODVANO AS CHAR(6)) RETURNS INT
AS
BEGIN
DECLARE @EXISTS INT
SET @EXISTS = 0
IF (@CODVANO <> 'VTERRA')
BEGIN
SELECT
@EXISTS=(CASE WHEN CODICE IS NOT NULL THEN -1 ELSE 0 END)
FROM dbo.VANI
WHERE (CODICE = @CODVANO)
END
RETURN(@EXISTS)
END
-- Create CHECK CONSTRAINT
ALTER TABLE dbo.VANI WITH NOCHECK ADD CONSTRAINT CHK_CODICE_VANO CHECK (dbo.UDF_UNIQUE_COD_VANO(CODICE)=0)
Proviamo ora a codificare un nuovo vano nel magazzino "Semilavorati", si tratta del “Cassetto 003” il cui codice “CAS003” non esiste nella tabella dbo.Vani. Il seguente comando di INSERT non potrà essere eseguito per la limitazione introdotta dal vincolo CHECK. Il vano che stiamo tentando di inserire non è effettivamente presente, ma il CHECK constraint blocca comunque l’inserimento.
INSERT INTO [dbo].[Vani] (CODICE, DESCRIZIONE, IDMAGAZZINI)
Select 'CAS003', 'Cassetto 003', (Select Id From Magazzini Where Codice='SMLA')
Msg 547, Level 16, State 0, Line 1The INSERT statement conflicted with the CHECK constraint "CHK_CODICE_VANO". The conflict occurred in database "TestDB", table "dbo.Vani", column 'CODICE'.The statement has been terminated.
Cosa sta succedendo ? Ho investito un po’ di tempo per verificare la situazione, analizziamo la sequenza degli eventi e delle attività eseguite da SQL Server durante l’operazione di INSERT. La figura seguente illustra la traccia SQL Profiler:
