Questo articolo ci mostra come aggiungere i vincoli di Default, i vincoli di Primary Key ed i vincoli di Foreign Key alle tabelle del nostro database di esempio.
Nei post precedenti, abbiamo generato le tabelle del nostro database di esempio, ed abbiamo generato le primary keys di tutte le tabelle riportiamo l’immagine del suo schema e andiamo ad aggiungere il necessario a creare le relazioni ed i vincoli di default dei dati inseriti.
I Vincoli di default
Servono per fare in modo che il valore di una colonna in una tabella assuma automaticamente un valore quando viene generata, nel caso del nostro database, faremo in modo che l’ID univoco delle tabelle del database con chiave primaia di tipo GUID (Uniqueidentifier) contengano sempre un valore quando inseriamo una riga.
Prima fase, cancelliamo tutti i vincoli di Default se esistono
/*Eliminamo tutti i Default per le tabelle, In questo caso l'inizializzazione automatica delle colonne Primary Key di tipo GUID (Uniqueidentifier)*/
/*We drop all Defaults for the tables, in this case the automatic initialization of The primary Key columns of type GUID (Uniqueidentifier)*/
IF EXISTS (SELECT 1 FROM sys.default_constraints WHERE object_id = OBJECT_ID('[dbo].[DF_TbIngredients_IDIngredient]') AND parent_object_id = OBJECT_ID('[dbo].[TbIngredients]')) BEGIN
PRINT 'DROP CONSTRAINT [DF_TbIngredients_IDIngredient] ON [TbIngredients]'
ALTER TABLE [dbo].[TbIngredients] DROP CONSTRAINT [DF_TbIngredients_IDIngredient]
END
GO
IF EXISTS (SELECT 1 FROM sys.default_constraints WHERE object_id = OBJECT_ID('[dbo].[DF_TbRecipeIngredients_IDRecipeIngredient]') AND parent_object_id = OBJECT_ID('[dbo].[TbRecipeIngredients]')) BEGIN
PRINT 'DROP CONSTRAINT [DF_TbRecipeIngredients_IDRecipeIngredient] ON [TbRecipeIngredients]'
ALTER TABLE [dbo].[TbRecipeIngredients] DROP CONSTRAINT [DF_TbRecipeIngredients_IDRecipeIngredient]
END
GO
IF EXISTS (SELECT 1 FROM sys.default_constraints WHERE object_id = OBJECT_ID('[dbo].[DF_TbRecipeLinks_IDRecipeLink]') AND parent_object_id = OBJECT_ID('[dbo].[TbRecipeLinks]')) BEGIN
PRINT 'DROP CONSTRAINT [DF_TbRecipeLinks_IDRecipeLink] ON [TbRecipeLinks]'
ALTER TABLE [dbo].[TbRecipeLinks] DROP CONSTRAINT [DF_TbRecipeLinks_IDRecipeLink]
END
GO
IF EXISTS (SELECT 1 FROM sys.default_constraints WHERE object_id = OBJECT_ID('[dbo].[DF_TbRecipes_IDRecipe]') AND parent_object_id = OBJECT_ID('[dbo].[TbRecipes]')) BEGIN
PRINT 'DROP CONSTRAINT [DF_TbRecipes_IDRecipe] ON [TbRecipes]'
ALTER TABLE [dbo].[TbRecipes] DROP CONSTRAINT [DF_TbRecipes_IDRecipe]
END
GO
Ometto la parte iniziale dello script che sarà uguale ai precedenti, permettendo di indicare il nome del database e dando un ID ed una versione allo script.
Presumo che la prima domanda che vi ponete guardando questo script è, ma perché cancellare dei vincoli che sappiamo non ci sono? Per un motivo molto semplice, perché se per qualche motivo abbiamo un database che ha dei problemi e vogliamo rigenerare tutti i suoi vincoli, basta eseguire di nuovo questo script e siamo sicuri che sono tutti a posto, non solo, se ci accorgessimo di aver sbagliato qualcosa nello script e lo correggessimo a posteriori, basterebbe rieseguirlo per essere certi che tutto è a posto.
IF EXISTS (SELECT 1 FROM sys.default_constraints WHERE object_id = OBJECT_ID('[dbo].[DF_TbRecipes_IDRecipe]') AND parent_object_id = OBJECT_ID('[dbo].[TbRecipes]')) BEGIN
PRINT 'DROP CONSTRAINT [DF_TbRecipes_IDRecipe] ON [TbRecipes]'
ALTER TABLE [dbo].[TbRecipes] DROP CONSTRAINT [DF_TbRecipes_IDRecipe]
END
Vediamo nel dettaglio come si cancella un vincolo di default, per prima cosa, bisogna verificare se esiste, per farlo dobbiamo saperne il nome e la tabella che lo contiene, quindi facciamo una richiesta alla vista di sistema sys.default_constraints chiedendogli, “scusa, hai un vincolo di nome DF_TbRecipes_IDRecipe sulla tabella TbRecipes?” Se lui risponde di si procediamo alla cancellazione.
Vediamo ora di rigenerare i vincoli, sia che non esistano sia che li abbiamo appena cancellati.
/*Rigeneriamo i default per la generazione automatica del valore di tutti i campi Primary Key di tipo GUID (Uniqueidentifier)*/
/*We re-create all defaults to automatically create a value for the Primary Key fields of GUID type (Uniqueidentifier)*/
IF NOT EXISTS (SELECT 1 FROM sys.default_constraints WHERE object_id = OBJECT_ID('[dbo].[DF_TbIngredients_IDIngredient]') AND parent_object_id = OBJECT_ID('[dbo].[TbIngredients]')) BEGIN
PRINT 'ADD CONSTRAINT [DF_TbIngredients_IDIngredient] ON [TbIngredients]'
ALTER TABLE [dbo].[TbIngredients] ADD CONSTRAINT [DF_TbIngredients_IDIngredient] DEFAULT (newid()) FOR [IDIngredient]
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.default_constraints WHERE object_id = OBJECT_ID('[dbo].[DF_TbRecipeIngredients_IDRecipeIngredient]') AND parent_object_id = OBJECT_ID('[dbo].[TbRecipeIngredients]')) BEGIN
PRINT 'ADD CONSTRAINT [DF_TbRecipeIngredients_IDRecipeIngredient] ON [TbRecipeIngredients]'
ALTER TABLE [dbo].[TbRecipeIngredients] ADD CONSTRAINT [DF_TbRecipeIngredients_IDRecipeIngredient] DEFAULT (newid()) FOR [IDRecipeIngredient]
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.default_constraints WHERE object_id = OBJECT_ID('[dbo].[DF_TbRecipeLinks_IDRecipeLink]') AND parent_object_id = OBJECT_ID('[dbo].[TbRecipeLinks]')) BEGIN
PRINT 'ADD CONSTRAINT [DF_TbRecipeLinks_IDRecipeLink] ON [TbRecipeLinks]'
ALTER TABLE [dbo].[TbRecipeLinks] ADD CONSTRAINT [DF_TbRecipeLinks_IDRecipeLink] DEFAULT (newid()) FOR [IDRecipeLink]
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.default_constraints WHERE object_id = OBJECT_ID('[dbo].[DF_TbRecipes_IDRecipe]') AND parent_object_id = OBJECT_ID('[dbo].[TbRecipes]')) BEGIN
PRINT 'ADD CONSTRAINT [DF_TbRecipes_IDRecipe] ON [TbRecipes]'
ALTER TABLE [dbo].[TbRecipes] ADD CONSTRAINT [DF_TbRecipes_IDRecipe] DEFAULT (newid()) FOR [IDRecipe]
END
GO
Anche in questo caso, prima di generare un vincolo controlliamo che non esista, questo ci permette semplicemente di essere certi che anche copiando un pezzo di codice o eseguendo solo una parte del codice dello script funziona.
I Vincoli di Foreign Key
Per poter relazionare le tabelle, si utilizzano i vincoli di Foreign Key (Chiave esterna) che sono rappresentati nel diagramma dalle linee che collegano le tabelle una all’altra, una Chiave primaria impone un vincolo forte ad un campo di tabella, infatti, tale campo può contenere solo valori che rispondono al campo corrispondente della tabella collegata oppure il valore NULL (DbNull in .Net).
Vediamo lo script e poi commentiamo un esempio per capire che cos’è la relazione che impostiamo.
/*Eliminiamo tutti i vincoli di Foreign Key fra le tabelle del database*/
/*We drop all foreign key constraints between the database tables*/
IF EXISTS (SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID('[dbo].[FK_TbIngredients_TbMeasureUnits]') AND parent_object_id = OBJECT_ID('[dbo].[TbIngredients]')) BEGIN
PRINT 'DROP CONSTRAINT [FK_TbIngredients_TbMeasureUnits] ON [TbIngredients]'
ALTER TABLE [dbo].[TbIngredients] DROP CONSTRAINT [FK_TbIngredients_TbMeasureUnits]
END
GO
IF EXISTS (SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID('[dbo].[FK_TbRecipeIngredients_TbIngredients]') AND parent_object_id = OBJECT_ID('[dbo].[TbRecipeIngredients]')) BEGIN
PRINT 'DROP CONSTRAINT [FK_TbRecipeIngredients_TbIngredients] ON [TbRecipeIngredients]'
ALTER TABLE [dbo].[TbRecipeIngredients] DROP CONSTRAINT [FK_TbRecipeIngredients_TbIngredients]
END
GO
IF EXISTS (SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID('[dbo].[FK_TbRecipeIngredients_TbRecipes]') AND parent_object_id = OBJECT_ID('[dbo].[TbRecipeIngredients]')) BEGIN
PRINT 'DROP CONSTRAINT [FK_TbRecipeIngredients_TbRecipes] ON [TbRecipeIngredients]'
ALTER TABLE [dbo].[TbRecipeIngredients] DROP CONSTRAINT [FK_TbRecipeIngredients_TbRecipes]
END
GO
IF EXISTS (SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID('[dbo].[FK_TbRecipeLinks_TbRecipes]') AND parent_object_id = OBJECT_ID('[dbo].[TbRecipeLinks]')) BEGIN
PRINT 'DROP CONSTRAINT [FK_TbRecipeLinks_TbRecipes] ON [TbRecipeLinks]'
ALTER TABLE [dbo].[TbRecipeLinks] DROP CONSTRAINT [FK_TbRecipeLinks_TbRecipes]
END
GO
Come negli script precedenti, verifichiamo se esistono ed eliminiamo tutti i vincoli di Foreign Key, questo ci permette di rieseguire in sicurezza questo script ma soprattutto di rigenerare gli indici quando necessario.
/*Rigeneriamo tutte le Foregn Key fra le tabelle del database*/
/*We re-create all Foreign Key constraints between the tables of the Database*/
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID('[dbo].[FK_TbIngredients_TbMeasureUnits]') AND parent_object_id = OBJECT_ID('[dbo].[TbIngredients]')) BEGIN
PRINT 'ADD CONSTRAINT [FK_TbIngredients_TbMeasureUnits] ON [TbIngredients]'
ALTER TABLE [dbo].[TbIngredients] ADD CONSTRAINT [FK_TbIngredients_TbMeasureUnits] FOREIGN KEY([IDMeasureUnit]) REFERENCES [dbo].[TbMeasureUnits] ([IDMeasureUnit])
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID('[dbo].[FK_TbRecipeIngredients_TbIngredients]') AND parent_object_id = OBJECT_ID('[dbo].[TbRecipeIngredients]')) BEGIN
PRINT 'ADD CONSTRAINT [FK_TbRecipeIngredients_TbIngredients] ON [TbRecipeIngredients]'
ALTER TABLE [dbo].[TbRecipeIngredients] ADD CONSTRAINT [FK_TbRecipeIngredients_TbIngredients] FOREIGN KEY([IDIngredient]) REFERENCES [dbo].[TbIngredients] ([IDIngredient])
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID('[dbo].[FK_TbRecipeIngredients_TbRecipes]') AND parent_object_id = OBJECT_ID('[dbo].[TbRecipeIngredients]')) BEGIN
PRINT 'ADD CONSTRAINT [FK_TbRecipeIngredients_TbRecipes] ON [TbRecipeIngredients]'
ALTER TABLE [dbo].[TbRecipeIngredients] ADD CONSTRAINT [FK_TbRecipeIngredients_TbRecipes] FOREIGN KEY([IDRecipe]) REFERENCES [dbo].[TbRecipes] ([IDRecipe])
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID('[dbo].[FK_TbRecipeLinks_TbRecipes]') AND parent_object_id = OBJECT_ID('[dbo].[TbRecipeLinks]')) BEGIN
PRINT 'ADD CONSTRAINT [FK_TbRecipeLinks_TbRecipes] ON [TbRecipeLinks]'
ALTER TABLE [dbo].[TbRecipeLinks] ADD CONSTRAINT [FK_TbRecipeLinks_TbRecipes] FOREIGN KEY([IDRecipe]) REFERENCES [dbo].[TbRecipes] ([IDRecipe])
END
GO
IF NOT EXISTS (SELECT 1 FROM TbVersions WHERE IDVersion = '2013.00.00.00_03')
INSERT INTO TbVersions (IDVersion, UpdateDescription, PrgVersion) VALUES ('2013.00.00.00_03', '03-CreateConstraints', '2013.0.0.0');
GO
Il vincolo di Foreign Key coinvolge sempre due tabelle, anche in questo caso, se pure superfluo controlliamo che il vincolo non esista prima di rigenerarlo, sempre per evitare errori e stranezze. Ma vediamo in cosa consiste il vincolo e come funziona:
ALTER TABLE [dbo].[TbIngredients] ADD CONSTRAINT [FK_TbIngredients_TbMeasureUnits] FOREIGN KEY([IDMeasureUnit]) REFERENCES [dbo].[TbMeasureUnits] ([IDMeasureUnit])
Il vincolo qui sopra, dice la seguente cosa al database, il campo IDMeasureUnit di una riga della tabelle TbIngredients, può contenere solo uno dei valori contenuti nella colonna IDMeasureUnit della tabella TbMeasureUnits oppure un NULL (DbNull). Questo tipo di vincolo ci assicura la consistenza dei nostri dati, impedendo che qualsiasi programma possa inserire in una tabella valori inconsistenti, quindi non coerenti con il vincolo imposto.
Alla fine dello script, come nei precedenti, aggiorniamo la tabella versioni del database, in modo che i programmi possano verificare che il Database sia creato ed aggiornato prima di iniziare a inserirvi dati.
Conclusioni
Abbiamo creato tutti i vincoli necessari al suo funzionamento nel nostro database di esempio, nel prossimo post, vedremo come fare ad aggiornare il nostro database modificando una tabella ed aggiungendo una nuova tabella a quelle esistenti.
Gli script T-SQL del codice di esempio sono scaricabili al seguente link e sono comprensivi di tutto quello che è spiegato nella breve serie di articoli.
