MSDE Altre istruzioni per l'uso (parte 5) - Creare/Eliminare Vincoli e Relazioni

MSDE Altre istruzioni per l'uso (parte 5) - Creare/Eliminare Vincoli su una Tabella
a cura di Sabrina Cosolo (requisiti: Livello intemedio di conoscenza generale)

Creare Vincoli e Relazioni su una tabella Via Codice SQL
La creazione di vincoli e relazioni sulle tabelle è un comando SQL che fa parte di quello che viene chiamato DDL (Data Definition Language). Deve essere effettuate collegandosi a SQL Server con un utente che abbia diritti di amministrazione sul database in cui si vuole generare il vincolo; è opportuno utilizzare sempre l'utente SA o un utente Windows del gruppo Builtin\Administrator per questo tipo di istruzioni.

Di seguito il codice SQL Per la creazione dei vincoli sulle tabelle del nostro database di esempio. Li genereremo per tipologia di vincolo e tabella e poi spiegheremo uso e sintassi dei vari vincoli.
Chiavi Primarie; vanno generate per prime su tutte le tabelle. Come detto in precedenza, solitamente vengono inserite nel comando CREATE TABLE, ma ai fini didattici e soprattutto da parte di chi le userà in progetti molto corposi, separare la creazione tabelle e la creazione vincoli può essere una scelta opportuna.
  ALTER TABLE [dbo].[tbBrani] 
    WITH NOCHECK 
    ADD CONSTRAINT [PK_tbBrani] 
    PRIMARY KEY  NONCLUSTERED 
    ([idBrano])  
    ON [PRIMARY]
  ALTER TABLE [dbo].[tbCaseDisc] 
    WITH NOCHECK 
    ADD CONSTRAINT [PK_tbCaseDisc] 
    PRIMARY KEY  NONCLUSTERED 
    ([idCasaDisc])  
    ON [PRIMARY]
  ALTER TABLE [dbo].[tbCate] 
    WITH NOCHECK 
    ADD CONSTRAINT [PK_tbCate] 
    PRIMARY KEY  NONCLUSTERED 
    ([idCate])  
    ON [PRIMARY]
  ALTER TABLE [dbo].[tbCatMusicali] 
    WITH NOCHECK 
    ADD CONSTRAINT [PK_tbCatMusicali] 
    PRIMARY KEY  NONCLUSTERED 
    ([idCatMusicale]) 
    ON [PRIMARY]
  ALTER TABLE [dbo].[tbCd] 
    WITH NOCHECK ADD CONSTRAINT [PK_tbCd] 
    PRIMARY KEY NONCLUSTERED 
    ([idcd]) 
    ON [PRIMARY]
  ALTER TABLE [dbo].[tbInterpreti] 
    WITH NOCHECK ADD CONSTRAINT [PK_tbInterpreti] 
    PRIMARY KEY  NONCLUSTERED 
    ([idInterprete])  
    ON [PRIMARY]
  ALTER TABLE [dbo].[tbRitmi] 
    WITH NOCHECK 
    ADD CONSTRAINT [PK_tbRitmi] 
    PRIMARY KEY  NONCLUSTERED 
    ([idRitmo])  
    ON [PRIMARY]
Vincoli di relazione: Questo tipo di vincoli sono i più comunemente usati nei DB relazionali, permettono di mettere in relazione due tabelle utilizzando un campo chiave esterna (foreign key) sulla tabella "collegante" che deve essere chiave primaria sulla tabella collegata. Ovvero, nel caso del nostro database, ogni brano musicale appartiene ad una categoria, il campo idCatMusicale sulla tabella brani è foreign key collegata al campo idCatMusicale sulla tabella categorie musicali di cui detto campo è chiave primaria. La creazione di questo vincolo permette la gestione dell'integrità referenziale fra le due tabelle ovvero ogni brano dovrà avere come riferimento una categoria musicale esistente o nessuna categoria, una categoria musicale assegnata ad almeno un brano non potrà essere eliminata se non dopo essere stata cancellata da tutti i brani che la contengono. Spero questa mini spiegazione possa aiutare i meno esperti, dato che la teoria dei database relazionali non fa parte dello scopo di questi articoli.

Vediamo ora gli script per la generazione dei vincoli di relazione per tutte le tabelle del nostro DB esempio.
  ALTER TABLE [dbo].[tbBrani] 
    ADD CONSTRAINT [FK_tbBrani_tbCatMusicali]
    FOREIGN KEY ([idCatMuscale]) 
    REFERENCES [dbo].[tbCatMusicali] ([idCatMusicale]) 
    NOT FOR REPLICATION
  ALTER TABLE [dbo].[tbBrani] 
    ADD CONSTRAINT [FK_tbBrani_tbCd] 
    FOREIGN KEY ([idcd]) 
    REFERENCES [dbo].[tbCd] ([idcd]) 
    NOT FOR REPLICATION
  ALTER TABLE [dbo].[tbBrani] 
    ADD CONSTRAINT [FK_tbBrani_tbInterpreti] 
    FOREIGN KEY ([idInterprete]) 
    REFERENCES [dbo].[tbInterpreti] ([idInterprete]) 
    NOT FOR REPLICATION
  ALTER TABLE [dbo].[tbBrani] 
    ADD CONSTRAINT [FK_tbBrani_tbRitmi] 
    FOREIGN KEY ([idRitmo]) 
    REFERENCES [dbo].[tbRitmi] ([idRitmo]) 
    NOT FOR REPLICATION

  ALTER TABLE [dbo].[tbCd] 
    ADD CONSTRAINT [FK_tbCd_tbCaseDisc] 
    FOREIGN KEY([idCasaDisc]) 
    REFERENCES [dbo].[tbCaseDisc] ([idCasaDisc]) 
    NOT FOR REPLICATION
  ALTER TABLE [dbo].[tbCd] 
    ADD CONSTRAINT [FK_tbCd_tbCate] 
    FOREIGN KEY ([idCate]) 
    REFERENCES [dbo].[tbCate] ([idCate]) 
    NOT FOR REPLICATION
  ALTER TABLE [dbo].[tbCd] 
    ADD CONSTRAINT [FK_tbCd_tbInterpreti] 
    FOREIGN KEY ([idInterprete]) 
    REFERENCES [dbo].[tbInterpreti] ([idInterprete]) 
    NOT FOR REPLICATION
Vincoli di Unicità: Questo tipo di vincoli serve per mettere dei "paletti" alla fantasia degli utenti del database; abbiamo già visto sopra che il vincolo Primary key deve essere predisposto su un campo o una serie di campi con un valore univoco in tutta la tabella, però spesso è necessario che anche altri campi di una tabella siano univoci, ad esempio se costruissimo un piano dei conti per la contabilità, potremmo generarlo con una primary key numerica in modo che sia possibile all'utente variarne la struttura in caso di necessità, ma il codice strutturale del piano dei conti che permetterà all'utente di assegnare ogni conto ad una determinata sezione del bilancio dovrà essere univoco onde evitare che spostando un conto vi siano delle stranezze nel calcolo del nostro bilancio. Ecco perché utilizzare un vincolo di unicità su un campo (o più campi) che non sia(no) chiave primaria.
L'uso che ne ho fatto io in questo database è un uso più semplice e costituisce per me una pratica sana, ovvero fare in modo che le tabelle di tipo Codice Descrizione abbiano descrizioni univoche e quindi l'utente non inserisca 3 codici diversi per la categoria musicale Folk, perché sbadatamente non si accorge che ne esiste già uno. Un altro motivo per generare un vincolo di unicità è che SQL Server su tutti i campi con vincolo di unicità costruisce automaticamente un indice ed è possibile indicargli se vogliamo che questo sia l'indice "fisico" con cui memorizzare i dati in tabella. Ecco il significato delle clausole NON CLUSTERED poste sui vincoli di primary key e CLUSTERED sui vincoli di unicità: la clausola CLUSTERED farà in modo che nelle combo box in cui appaiono queste tabelle le descrizioni appaiano in ordine alfabetico senza che dobbiamo ricordarci di indicare la clausola "ORDER BY" nella stringa sql di lettura dati sul database.
  ALTER TABLE [dbo].[tbCatMusicali] 
    ADD CONSTRAINT [Uq_tbCatMusicali_Nome]
    UNIQUE CLUSTERED (Nome)
 
  ALTER TABLE [dbo].[tbCate] 
    ADD CONSTRAINT [Uq_tbCate_Nome]
    UNIQUE CLUSTERED (Nome)
 
  ALTER TABLE [dbo].[tbRitmi] 
    ADD CONSTRAINT [Uq_tbRitmi_Nome]
    UNIQUE CLUSTERED (Nome)
Diamo un significato agli elementi usati nella sintassi dei comandi utilizzati:

ALTER TABLE (nome tabella): E' il comando utilizzato su una tabella generata e riempita con dati per modificarne la struttura in qualsiasi modo, in questo caso lo useremo per aggiungere uno o più vincoli, potremmo usarlo allo stesso modo per aggiungere colonne o per togliere colonne dalla tabella così come per eliminare vincoli sulla stessa.

WITH NOCHECK: Viene applicato quando si modificano tabelle esistenti per aggiungere dei vincoli per disattivare il controllo sul vincolo per i dati già inseriti, questo permette di modificare la tabella ed eventualmente fare una ricostruzione successiva per aggiornare i dati già inseriti.

ADD CONSTRAINT (NomeConstraint): Qesto è il comando generico per l'aggiunta di ogni tipo di vincolo (constraint) su una tabella. Il nome del constraint segue le regole relative ai nomi degli oggetti database, perciò può contenere lettere, numeri, spazi e caratteri speciali, sconsiglio vivamente l'uso dei caratteri accentati o degli spazi men che meno di trattini e underscore, se proprio non ne potete fare a meno usate gli underscore.

Tipo vincolo: Nei nostri esempi abbiamo utilizzato i tre vincoli più semplici che hanno la seguente sintassi

PRIMARY KEY = chiave primaria

FOREIGN KEY = chiave esterna

UNIQUE = campo univoco

[NON] CLUSTERED: per le Primary Keys e Unique constraints indica se l'indice che l'RDBMS costruirà sul vincolo (ricordo che le chiavi primarie e i vincoli di unicità hanno sempre un indice associato) deve essere utilizzato per l'ordinamento fisico dei dati della tabella oppure no. Ogni tabella può avere un solo indice CLUSTERED per ovvie ragioni non è possibile scrivere fisicamente su disco una tabella con due diversi ordinamenti.

fieldname1 - fieldnameN: sono i nomi dei campi associati al vincolo sebbene abbiamo utilizzato solo chiavi primarie ed univoche con un singolo campo nei nostri esempi, entrambi i tipi di vincolo possono essere formati da più campi della tabella (es. piano dei conti, campo mastro+campo sottoconto). Lo stesso vale per le foreign key, potrei ad esempio avere una foreign key che dai documenti referenzia il piano dei conti con un campo tipo conto (cliente fornitore) e un campo codice conto (ad esempio per dare lo stesso codice a un cliente che è anche fornitore).

REFERENCES nometabella (fieldname1 - fieldnameN): Indica qual'è la tabella referenziata e quali sono i campi che compongono la relazione per le FOREIGN KEY.

NOT FOR REPLICATION Clausola che SQL server inserisce come default se non diversamente specificato e stabilisce che la chiave (primaria, univoca o altro) non verrà utilizzata per operazioni di replica. I vincoli di replica sono utilizzati quando abbiamo un sistema centralizzato con altri sistemi collegati su cui i database vengono pubblicati (replicati); andiamo nel campo della "fantascienza" per noi comuni programmatori.

ON PRIMARY specifica in quale filegroup (vedi) l'indice legato al vincolo deve essere creato.

Eliminare un vincolo o una relazione via codice SQL
Come sempre eliminare è più facile che creare perciò ecco i comandi per eliminare i vincoli da noi costruiti:
  ALTER TABLE [dbo].[tbBrani] DROP CONSTRAINT FK_tbBrani_tbCatMusicali
  ALTER TABLE [dbo].[tbBrani] DROP CONSTRAINT FK_tbBrani_tbCd
  ALTER TABLE [dbo].[tbBrani] DROP CONSTRAINT FK_tbBrani_tbInterpreti
  ALTER TABLE [dbo].[tbBrani] DROP CONSTRAINT FK_tbBrani_tbRitmi
  ALTER TABLE [dbo].[tbCaseDisc] DROP CONSTRAINT UQ_tbCaseDisc_Nome
  ALTER TABLE [dbo].[tbCatMusicali] DROP CONSTRAINT UQ_tbCatMusicali_Nome
  ALTER TABLE [dbo].[tbCate] DROP CONSTRAINT UQ_tbCate_Nome
  ALTER TABLE [dbo].[tbCd] DROP CONSTRAINT FK_tbCd_tbCaseDisc
  ALTER TABLE [dbo].[tbCd] DROP CONSTRAINT FK_tbCd_tbCate
  ALTER TABLE [dbo].[tbCd] DROP CONSTRAINT FK_tbCd_tbInterpreti
  ALTER TABLE [dbo].[tbInterpreti] DROP CONSTRAINT FK_tbInterpreti_tbInterpreti
  ALTER TABLE [dbo].[tbInterpreti] DROP CONSTRAINT CK_tbInterpreti_TiPers
  ALTER TABLE [dbo].[tbInterpreti] DROP CONSTRAINT UQ_tbInterpreti_Nome
  ALTER TABLE [dbo].[tbRitmi] DROP CONSTRAINT UQ_tbRitmi_Nome
 
  ALTER TABLE [dbo].[tbBrani] DROP CONSTRAINT PK_tbBrani
  ALTER TABLE [dbo].[tbCaseDisc] DROP CONSTRAINT PK_tbCaseDisc
  ALTER TABLE [dbo].[tbCate] DROP CONSTRAINT PK_tbCate
  ALTER TABLE [dbo].[tbCatMusicali] DROP CONSTRAINT PK_tbCatMusicali
  ALTER TABLE [dbo].[tbCd] DROP CONSTRAINT PK_tbCd
  ALTER TABLE [dbo].[tbInterpreti] DROP CONSTRAINT PK_tbInterpreti
La sintassi di base del comando è:
  ALTER TABLE [[owner].[tablename] DROP CONSTRAINT [NomeConstraint]
Dove owner e tablename corrispondono alle definizioni date in precedenza, e NomeConstraint è il nome che abbbiamo assegnato al vincolo.
Faccio notare come nella precedente serie di comandi sql l'eliminazione dei vincoli di chiave primaria costituisca l'ultima operazione, infatti non è possibile eliminare un vincolo di chiave primaria da una tabella se questa tabella ha una relazione di FOREIGN KEY con un'altra tabella sulla propria chiave primaria.

Anche per questi comandi SQL si può utilizzare OSQL.EXE (vedi, anche per i parametri).

La sintassi per chiamare un comando SQL usando OSQL è la seguente:
  osql -S Localhost -U SA -P as -i vincolisql.sql
un possibile file di input da utilizzare per realizzare uno degli esempi vincolisql.sql è un file di testo contenente il comando da eseguire che è leggermente diverso da quello sopra descritto, riporto la sintassi per la creazione delle chiavi primarie:
  USE MusicBox
  GO
 
  ALTER TABLE [dbo].[tbBrani] 
    ADD CONSTRAINT [FK_tbBrani_tbCatMusicali]
    FOREIGN KEY ([idCatMuscale]) 
    REFERENCES [dbo].[tbCatMusicali] ([idCatMusicale]) 
    NOT FOR REPLICATION
  ALTER TABLE [dbo].[tbBrani] 
    ADD CONSTRAINT [FK_tbBrani_tbCd] 
    FOREIGN KEY ([idcd]) 
    REFERENCES [dbo].[tbCd] ([idcd]) 
    NOT FOR REPLICATION
  ALTER TABLE [dbo].[tbBrani] 
    ADD CONSTRAINT [FK_tbBrani_tbInterpreti] 
    FOREIGN KEY ([idInterprete]) 
    REFERENCES [dbo].[tbInterpreti] ([idInterprete]) 
    NOT FOR REPLICATION
  ALTER TABLE [dbo].[tbBrani] 
    ADD CONSTRAINT [FK_tbBrani_tbRitmi] 
    FOREIGN KEY ([idRitmo]) 
    REFERENCES [dbo].[tbRitmi] ([idRitmo]) 
    NOT FOR REPLICATION 
  GO
 
  QUIT
Il comando USE MUSICBOX serve a dire al server che dobbiamo lavorare sul Database Musicbox database di esempio creato nel precedente articolo), Il comando GO esegue i comandi richiesti, il comando QUIT esce da OSQL.EXE che altrimenti si comporta come un Interprete comandi e rimane attivo all'interno della finestra console.

La sintassi da usare con OSQL per trasportarvi tutti gli script da noi generati in questa pagina è la seguente:
  USE Musicbox
  GO 
  
  script da eseguire
 
  GO
 
  QUIT
<<< Creare/Eliminare Tabelle
Creare/Eliminare Viste >>>