Progettazione dell`integrità dei dati

C0862254X.fm Page 419 Sunday, March 11, 2007 12:53 PM
Capitolo 8
Progettazione dell’integrità dei dati
Nella sua forma più semplice, la progettazione dell’integrità dei dati riguarda ciò che è
necessario fare per garantire la qualità dei dati presenti nel database. La qualità e l’utilità di un
database è direttamente proporzionale alla qualità dei dati contenuti nelle tabelle al suo
interno. Se parte dei dati memorizzati nelle tabelle è costituita da informazioni inutili, la
fiducia nell’accuratezza dei dati ottenuti dal database diminuisce.
I dati errati possono essere inseriti nel database in svariati modi. Il modo più ovvio è quello
in cui una persona incaricata dell’immissione dei dati commette un errore che non viene
rilevato. Questo errore rimane nel database fino a quando un’altra persona lo nota e lo
corregge manualmente. Un’altra forma di errore si verifica quando due aggiornamenti ai dati
del database entrano in conflitto e il database deve decidere in modo arbitrario che uno degli
aggiornamenti deve avere la precedenza sull’altro. Alla base di questo capitolo ci sono le
nozioni necessarie su come utilizzare il processo interno di Microsoft SQL Server 2005 per
risolvere questo tipo di conflitti e garantire che i dati siano formattati correttamente e
abbiano le proprietà appropriate.
Obiettivi d’esame contenuti in questo capitolo:
■
Progettare l’integrità dei dati.
❑
Risolvere i conflitti tra dati.
❑
Esplicitare i vincoli impliciti.
❑
Assegnare tipi di dati al controllo delle caratteristiche dei dati archiviati in una
colonna.
Lezioni nel presente capitolo:
■
Lezione 1: Risoluzione dei conflitti tra dati. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 421
■
Lezione 2: Esplicitazione dei vincoli impliciti . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 428
■
Lezione 3: Assegnazione dei tipi di dati per il controllo delle
caratteristiche dei dati memorizzati in una colonna . . . . . . . . . . . . . . . . . . . . . . . . . 442
419
C0862254X.fm Page 420 Sunday, March 11, 2007 12:53 PM
420
Capitolo 8
Progettazione dell’integrità dei dati
Operazioni preliminari
Per completare le lezioni di questo capitolo, è necessario aver eseguito le seguenti attività:
■
Configurazione di un computer Microsoft Windows Server 2003 R2 con SQL Server
2005 Enterprise Edition SP1 come descritto nell’appendice.
■
Installazione e aggiornamento di una copia del database di esempio AdventureWorks
come descritto nell’appendice
Per questo capitolo non è richiesta alcuna ulteriore configurazione.
C0862254X.fm Page 421 Sunday, March 11, 2007 12:53 PM
Lezione 1: Risoluzione dei conflitti tra dati
421
Lezione 1: Risoluzione dei conflitti tra dati
I conflitti tra dati sono inevitabili nei casi in cui è possibile modificare i dati di database
replicati in più ubicazioni. Tale inevitabilità deriva dalla latenza intrinseca nella replica tra
server di pubblicazione e sottoscrittori. Infatti esiste sempre la possibilità che durante
l’intervallo di replica due persone differenti in due ubicazioni separate possano modificare
una stessa informazione. Questa lezione illustra i processi che possono essere configurati in
SQL Server per risolvere questo tipo di conflitti.
Nota
Replica
Anche se il capitolo 10, “Replica”, illustra più dettagliatamente le implicazioni del processo di
replica di SQL Server 2005, i concetti espressi in questa lezione richiedono solamente di
conoscere il significato dei termini server di replica, sottoscrittore e articolo, i veri componenti
chiave del processo. Un server di replica è un’istanza di database che rende disponibili i dati ad
altre ubicazioni mediante la replica. Un sottoscrittore è un’istanza database che riceve i dati
replicati da uno o più server di replica. I sottoscrittori sono in grado di inoltrare le modifiche di
ritorno ai server di replica, oppure di ripubblicare i dati su altri sottoscrittori. Un articolo è un
oggetto database, ad esempio una tabella, presente all’interno di una pubblicazione.
Al termine di questa lezione, si sarà in grado di:
■
Capire il modo in cui SQL Server 2005 rileva i conflitti tra dati.
■
Capire le differenti norme di risoluzione dei conflitti disponibili in SQL Server 2005.
■
Configurare una norma di risoluzione dei conflitti.
Tempo previsto per la lezione: 30 minuti
Rilevazione dei conflitti
Quando viene creata una pubblicazione, il processo di replica aggiunge una colonna
Uniqueidentifier a tutte le tabelle presenti nell’articolo, come mostrato nella figura 8-1. Ogni
volta che i dati pubblicati vengono modificati sul server di replica o sul sottoscrittore, SQL
Server inserisce un nuovo GUID (Globally Unique IDentifier) nelle righe Uniqueidentifier
in cui si trovano i dati modificati. Durante la sincronizzazione tra server di replica e
sottoscrittore, l’agente di lettura coda confronta i valori precedente e corrente dei GUID per
determinare se esiste un conflitto.
Mentre avviene la sincronizzazione, la transazione nella coda conserva contemporaneamente il
vecchio e il nuovo GUID. Quando la transazione viene applicata al server di replica, i due
GUID nella transazione e il GUID nella relativa tabella nella pubblicazione vengono
confrontati. Se il vecchio GUID memorizzato all’interno della transazione risulta identico a
quello nella pubblicazione, SQL Server aggiorna la pubblicazione. Quando avviene questo
C0862254X.fm Page 422 Sunday, March 11, 2007 12:53 PM
422
Capitolo 8
Progettazione dell’integrità dei dati
aggiornamento, la colonna Uniqueidentifier della riga appropriata della tabella viene
assegnata al GUID generato dal sottoscrittore al momento della modifica. Il vecchio GUID
corrispondente a quello della pubblicazione indica che i dati di quella riga non sono stati
modificati in seguito all’ultima sincronizzazione dei dati sul sottoscrittore.
Figura 8-1 Aggiunta delle colonne Uniqueidentifier alle tabelle
Se il vecchio GUID memorizzato nella transazione non corrisponde a quello della
pubblicazione, SQL Server rileva un conflitto. La discrepanza indica che della riga esistono due
versioni differenti, una nella transazione inviata dal sottoscrittore e una più recente sul server di
replica. Ciò avviene quando un altro sottoscrittore aggiorna la stessa riga prima che la
transazione esistente sul sottoscrittore venga sincronizzata. Ad esempio, Server A è
configurato come server di replica per Server B e Server C. Si supponga che i dati presenti su
Server B e Server C vengano aggiornati nello stesso momento. Server B e Server C eseguono
la sincronizzazione con Server A. Server C si collega per primo e il suo “vecchio GUID riga
dati Server A” corrisponde al “GUID riga dati Server A” esistente; pertanto il nuovo GUID
generato viene assegnato su Server A. Quando Server B esegue la sincronizzazione, il suo
“vecchio GUID riga dati Server A” non corrisponde al nuovo GUID aggiornato su Server A
GUID, causando l’insorgere di un conflitto. Ciò che avviene in seguito dipende dalla norma di
risoluzione dei conflitti configurata come parte della pubblicazione.
Risoluzione dei conflitti
La norma di risoluzione dei conflitti da utilizzare viene selezionata durante la creazione di
una pubblicazione che impiega l’aggiornamento mediante code. La norma di risoluzione dei
C0862254X.fm Page 423 Sunday, March 11, 2007 12:53 PM
Lezione 1: Risoluzione dei conflitti tra dati
423
conflitti determina il modo in cui l’agente di lettura coda tratta le differenti versioni di una
stessa riga durante il processo di sincronizzazione. Esistono tre possibili norme di
risoluzione dei conflitti:
■
Mantieni la modifica del server di pubblicazione
■
Reinizializza sottoscrizione
■
Mantieni la modifica del sottoscrittore
Mantieni la modifica del server di pubblicazione è la norma predefinita. La norma di
risoluzione dei conflitti può essere modificata solamente in assenza di sottoscrizioni esistenti.
Per modificare la norma di risoluzione dei conflitti, eseguire le seguenti operazioni:
1. Aprire SQL Server Management Studio e connettersi all’istanza del database che opera
da server di pubblicazione.
2. Espandere il nodo Replica.
3. Espandere il nodo Pubblicazioni locali.
4. Fare clic con il pulsante destro del mouse sulla pubblicazione per cui si desidera
modificare la norma di conflitto e scegliere Proprietà. Viene aperta la finestra di
dialogo Proprietà pubblicazione.
5. Nel riquadro Selezione pagina, selezionare Opzioni sottoscrizione.
6. Nell’elenco a discesa accanto alla norma di risoluzione dei conflitti, selezionare la norma
desiderata, come mostrato nella figura 8-2.
Figura 8-2
Selezione di una norma di risoluzione dei conflitti
C0862254X.fm Page 424 Sunday, March 11, 2007 12:53 PM
424
Capitolo 8
Progettazione dell’integrità dei dati
La parte rimanente di questa sezione illustra il funzionamento delle tre norme di risoluzione
dei conflitti.
Mantieni la modifica del server di pubblicazione
Applicando a una pubblicazione la norma di risoluzione dei conflitti Mantieni la modifica del
server di pubblicazione, SQL Server mantiene la coerenza in base ai dati esistenti sul server di
replica. In caso di discrepanze, viene eseguito il rollback della transazione in conflitto sul
sottoscrittore. Fatto ciò, SQL Server aggiorna sul sottoscrittore le righe che hanno causato il
conflitto in modo da farle corrispondere a quelle presenti sul server di replica. Ad esempio,
Server A è configurato come server di replica, mentre Server B e Server C sono configurati
come sottoscrittori. Persone differenti modificano contemporaneamente la stessa riga di dati
su Server B e Server C. Server C esegue per primo la sincronizzazione con Server A. Al termine
della sincronizzazione, Server A contiene i dati aggiornati presenti su Server C. A questo punto
Server B tenta di eseguire la sincronizzazione con Server A, ma l’agente di lettura coda rileva la
presenta di un conflitto. La rilevazione del conflitto avvia un processo che annulla la modifica
dei dati su Server B e sovrascrive i dati della riga ubicata su Server B con i nuovi dati presenti
su Server A. In questo caso, il processo di risoluzione del conflitto ha dato la precedenza
all’aggiornamento verificatosi su Server C invece che a quello eseguito su Server A. Se Server
B avesse eseguito la sincronizzazione con il server di replica prima di Server C, l’aggiornamento
su Server B avrebbe avuto la precedenza e sarebbe stato replicato su tutti i server.
Reinizializza sottoscrizione
Implementando la norma Reinizializza sottoscrizione su una pubblicazione, il rilevamento di
un conflitto causa il rigetto di tutte le transazioni nella coda, compresa quella che ha dato
origine al conflitto. Questa opzione ha implicazioni più ampie della norma predefinita, poiché
vengono persi altri aggiornamenti non causa di conflitti. Ad esempio, un aggiornamento sul
server sottoscrittore B modifica i dati delle prime due righe di una tabella. Un aggiornamento
contemporaneo sul server sottoscrittore C modifica i dati della prima riga della stessa tabella.
Server C esegue per primo la sincronizzazione con il server di replica, con l’aggiornamento
della prima riga della tabella sul server di replica. Quando Server B tenta di eseguire la
sincronizzazione, l’agente di lettura coda rileva un conflitto e rigetta tutte le transazioni.
Pertanto, vengono perse le modifiche apportate alla prima e alla seconda riga su Server B.
Mantieni la modifica del sottoscrittore
Quando si applica la norma Mantieni la modifica del sottoscrittore a una pubblicazione,
viene conservata l’ultima transazione del sottoscrittore ad aggiornare il server di replica. In
caso di conflitto rilevato dall’agente di lettura coda, viene utilizzata la transazione inviata dal
sottoscrittore e aggiornato il server di replica. Nell’esempio appena visto in cui vengono
aggiornati i dati presenti su Server B e Server C e quest’ultimo esegue la sincronizzazione per
C0862254X.fm Page 425 Sunday, March 11, 2007 12:53 PM
Lezione 1: Risoluzione dei conflitti tra dati
425
primo, l’aggiornamento da Server B sostituisce quello da Server C sul server di replica,
poiché è l’ultimo a venire applicato.
Maggiori informazioni
Significato della risoluzione di un conflitto
Per ulteriori informazioni sulla rilevazione e la risoluzione dei conflitti negli aggiornamenti in
coda, consultare l’articolo MSDN all’indirizzo msdn2.microsoft.com/en-us/library/
ms151177(SQL.90,d=ide).aspx.
Verifica rapida
1. Quali sono i nomi delle tre norme di risoluzione dei conflitti?
2. Quale è il nome del componente che rileva la presenza di conflitti?
3. Quale norma di risoluzione dei conflitti è attiva se SQL Server elimina la coda
di transazione quando rileva un conflitto?
Risposta della verifica rapida
1. I nomi delle tre norme di risoluzione dei conflitti sono Mantieni la modifica del
server di pubblicazione, Mantieni la modifica del sottoscrittore e Reinizializza
sottoscrizione.
2. L’agente di lettura coda è il componente incaricato di rilevare i conflitti.
3. In questa situazione viene utilizzata la norma Reinizializza sottoscrizione.
Suggerimenti d’esame
Utilizzare un foglio di brutta. Quasi tutti i provider di esami
forniscono carta da usare per la brutta copia o una lavagna cancellabile da utilizzare durante
l’esame. Molti esaminandi sono troppo stressati dall’esperienza per sfruttare in modo
appropriato questa risorsa. A volte, gli autori delle domande forniscono una quantità tale di
informazioni da confondere facilmente le idee di chi affronta l’esame. Ad esempio, può accadere
di leggere di colonne con nomi simili in più tabelle differenti. Nell’ambiente di esame, è facile
confondere le colonne e le relative descrizioni quando ne vengono proposte più di una o due.
In tal caso, fermarsi un momento e annotare i nomi e le descrizioni delle colonne su un foglio
di appunti. Confrontarli con il testo della domanda per assicurarsi di averli trascritti
correttamente e quindi iniziare a lavorare sulla domanda posta. L’uso di una lista personale
risulta più facile che cercare nel paragrafo della domanda per scoprire se gli elementi
identificati sono quelli corretti.
Visualizzazione dei conflitti tra dati
I conflitti tra dati possono essere visualizzati utilizzando il Visualizzatore conflitti di replica
Microsoft. I dati in conflitto vengono memorizzati fino allo scadere del periodo predefinito
di conservazione del conflitto, solitamente di 14 giorni. Questo periodo può essere
modificato specificando un valore di conservazione per il parametro @conflict_retention della
stored procedure sp_addpublication.
C0862254X.fm Page 426 Sunday, March 11, 2007 12:53 PM
426
Capitolo 8
Progettazione dell’integrità dei dati
Se non vi sono conflitti rilevabili, il Visualizzatore conflitti di replica apre una finestra di
dialogo che indica che non esiste alcun conflitto e non è possibile procedere oltre. In caso di
presenza di conflitti, il Visualizzatore conflitti di replica permette di filtrare le righe,
selezionare e rimuovere righe dalla tabella di metadati del conflitto ed esportare i dettagli del
conflitto selezionando Registra informazioni dettagliate sul conflitto e immettendo il nome
del file in cui si desidera salvare i dettagli.
Maggiori informazioni
Visualizzazione dei conflitti di dati
Per ulteriori informazioni sulla visualizzazione dei conflitti di dati mediante il Visualizzatore conflitti
di replica Microsoft, consultare l’articolo MSDN all’indirizzo msdn2.microsoft.com/en-us/library/
ms151865(SQL.90,d=ide).aspx.
ESERCIZIO PRATICO
Visualizzazione dei conflitti
Quando si verifica un conflitto, è necessario esaminare la situazione per accertarsi che non
avvengano perdite di dati importanti durante il processo che deve risolverlo. La revisione
regolare dei dati in conflitto aiuta a determinare se la norma di risoluzione dei conflitti
utilizzata è appropriata. Per utilizzare il Visualizzatore conflitti di replica Microsoft per
rivedere i dati in conflitto, eseguire le seguenti operazioni:
1. Aprire SSMS e connettersi all’istanza del server di replica.
2. Espandere la cartella Replica.
3. Espandere la cartella Pubblicazioni locali.
4. Fare clic con il pulsante destro del mouse sulla pubblicazione da esaminare alla ricerca
di conflitti e selezionare Visualizza conflitti. Si apre una finestra di dialogo che indica
se non vi sono conflitti presenti.
5. Fare clic su OK.
Riepilogo della lezione
■
In presenza di più aggiornamenti contemporanei agli stessi dati su sottoscrittori
differenti, i conflitti si verificano quando tali sottoscrittori eseguono la successiva
sincronizzazione con il server di replica.
■
L’agente di lettura coda è il sottosistema di SQL Server incaricato di rilevare la presenza
di conflitti.
■
Le norme di risoluzione dei conflitti determinano il modo in cui il server di replica
deve gestire i conflitti.
■
Se viene applicata la norma di risoluzione dei conflitti Mantieni la modifica del server
di pubblicazione, quando si verifica un conflitto la transazione viene annullata sul
sottoscrittore e i dati presenti sul sottoscrittore vengono sovrascritti da quelli presenti
sul server di replica.
C0862254X.fm Page 427 Sunday, March 11, 2007 12:53 PM
Lezione 1: Risoluzione dei conflitti tra dati
427
■
Se viene applicata la norma di risoluzione dei conflitti Reinizializza sottoscrizione, la
rilevazione di un conflitto causa il rigetto di tutte le transazioni nella coda, compresa
quella che ha causato il conflitto in questione.
■
Se viene applicata la norma di risoluzione dei conflitti Mantieni la modifica del
sottoscrittore, viene mantenuta l’ultima transazione del sottoscrittore ad aggiornare il
server di replica.
■
I conflitti tra dati possono essere visualizzati utilizzando il Visualizzatore conflitti di
replica Microsoft. I dati in conflitto vengono memorizzati fino al termine del periodo
di conservazione del conflitto (per impostazione predefinita, 14 giorni).
Revisione della lezione
È possibile utilizzare le seguenti domande per verificare la propria conoscenza delle
informazioni presentate nella lezione 1, “Risoluzione dei conflitti tra dati”. Qualora si
preferisca eseguire la verifica in formato elettronico, tali domande sono disponibili anche nel
CD allegato.
Nota
Risposte
Le risposte alle domande e le relative spiegazioni sono incluse nella sezione “Risposte” nella
parte conclusiva del libro.
1. Quale norma di risoluzione dei conflitti assicura che il server di replica sia sempre
aggiornato quando un sottoscrittore esegue la sincronizzazione con nuovi dati?
A. Mantieni la modifica del server di pubblicazione
B. Reinizializza sottoscrizione
C. Mantieni la modifica del sottoscrittore
2. Quale sottosistema di SQL Server 2005 rileva i conflitti di dati durante la
sincronizzazione tra sottoscrittore e server di replica ?
A. SQL Server Integration Services
B. SQL Server Agent
C. Agente di lettura coda
D. SQL Server Analysis Services
3. Ian immette alcune modifiche per correggere una riga in un database sottoscrittore.
Quando accede nuovamente un’ora più tardi, egli nota che la riga è stata modificata ma
che le modifiche sono differenti da quelle immesse da lui. Eseguito un controllo,
scopre che si è verificato un conflitto. Quale delle seguenti norme di risoluzione dei
conflitti può essere applicata alla pubblicazione? (Scegliere tutte le risposte corrette.)
A. Mantieni la modifica del server di pubblicazione
B. Reinizializza sottoscrizione
C. Mantieni la modifica del sottoscrittore
C0862254X.fm Page 428 Sunday, March 11, 2007 12:53 PM
428
Capitolo 8
Progettazione dell’integrità dei dati
Lezione 2: Esplicitazione dei vincoli impliciti
Come alcuni avranno già appreso durante lo studio per altri esami precedenti di SQL Server
2005, i vincoli sono limitazioni dei dati che gli utenti possono inserire nelle colonne della
tabella di un database. I vincoli UNIQUE o PRIMARY KEY impediscono gli utenti di inserire
un valore già esistente nella colonna, mentre i vincoli CHECK evitano che essi inseriscano
valori che non soddisfano una particolare regola. Nel contesto di SQL Server 2005, un vincolo
implicito costituisce una regola aziendale. I vincoli impliciti possono comprendere pressoché
qualsiasi cosa, da “nessun appaltatore può fatturare più di 60 ore alla settimana” al numero
massimo di voci che un cliente può ordinare in un dato momento. L’amministratore del
database può essere incaricato di verificare che il database rispetti le regole dei criteri
aziendali. Viene definito vincolo esplicito quello che l’amministratore applica al database.
Partendo dall’esempio appena citato, l’amministratore del database può implementare un
vincolo esplicito programmando il database in modo tale da rendere impossibile
l’immissione da parte dell’appaltatore di un valore superiore a 60 ore la settimana nel
database di fatturazione della società.
Al termine di questa lezione, si sarà in grado di:
■
Esplicitare i vincoli impliciti.
■
Definire vincoli di tipo PRIMARY KEY.
■
Definire vincoli di tipo UNIQUE.
■
Definire vincoli di tipo CHECK.
■
Definire vincoli di tipo FOREIGN KEY.
■
Applicare definizioni di tipo DEFAULT alle colonne.
■
Configurare il supporto di valori NULL nelle colonne.
■
Definire i trigger.
Tempo previsto per la lezione: 30 minuti
Significato di vincoli impliciti ed espliciti
Un vincolo implicito può essere visto come una regola aziendale o dell’organizzazione. Un
possibile esempio può essere un’azienda in cui tutti i dipendenti di un particolare reparto
hanno un salario annuo compreso tra 40 mila e 60 mila dollari. Per rendere esplicito questo
vincolo implicito, è necessario stabilire un modo per garantire che il database non consenta
agli utenti di immettere valori al di fuori di tale intervallo.
C0862254X.fm Page 429 Sunday, March 11, 2007 12:53 PM
Lezione 2: Esplicitazione dei vincoli impliciti
429
Vincoli
I vincoli determinano se gli utenti possono memorizzare dati particolari in una colonna sulla
base di un’apposita regola. Questa può essere molto semplice, ad esempio “consenti la
memorizzazione in questa colonna di qualsiasi valore tranne quelli uguali ad altri già
presenti”. I vincoli possono anche essere più complessi, permettendo agli utenti di inserire
nelle colonne della tabella solo valori conformi a una particolare regola logica. Nelle
prossima pagine vengono illustrati i seguenti tipi di vincoli:
■
PRIMARY KEY
■
UNIQUE
■
FOREIGN KEY
■
CHECK
Vincoli PRIMARY KEY
Un vincolo PRIMARY KEY impone l’univocità dei dati e interrompe l’immissione di valori
NULL nelle colonne comprese al suo interno. Ogni tabella può avere un solo vincolo
PRIMARY KEY. Una colonna cui viene applicato un vincolo PRIMARY KEY non può
contenere valori NULL. Quando si specifica un vincolo PRIMARY KEY per una tabella, il
motore database impone l’univocità dei dati creando un indice univoco per la colonna chiave
primaria.
I vincoli PRIMARY KEY possono essere applicati a più colonne. Facendo ciò, la
combinazione dei valori di tutte le colonne comprese nella definizione del vincolo deve
risultare univoca. In questo modo, è possibile applicare un vincolo PRIMARY KEY alle
colonne FirstName e LastName anche se la colonna del cognome può contenere voci
duplicate. Ciò non sarebbe possibile in presenza di due persone con uguale nome e
cognome, benché sia possibile aggiungere una terza colonna contenente l’iniziale del terzo
nome e così via.
Per creare un vincolo PRIMARY KEY su una tabella esistente, eseguire le seguenti operazioni:
1. Aprire SSMS e connettersi all’istanza di database che ospita la tabella in cui si desidera
inserire la chiave primaria.
2. Espandere la cartella Database e localizzare il database contenente la tabella in cui si
desidera inserire la chiave primaria.
3. Espandere il database appropriato e localizzare la tabella in cui si desidera inserire la
chiave primaria.
4. Fare clic con il pulsante destro del mouse su tabella e scegliere Modifica.
C0862254X.fm Page 430 Sunday, March 11, 2007 12:53 PM
430
Capitolo 8
Progettazione dell’integrità dei dati
5. Verificare che non sia selezionata la casella di controllo Consenti valori NULL nella
colonna cui si desidera applicare la chiave primaria.
6. Fare clic con il pulsante destro del mouse sulla colonna e scegliere Imposta chiave
primaria dal menu che appare, mostrato nella figura 8-3.
Figura 8-3 Configurazione di una chiave primaria
7. Nella barra degli strumenti, fare clic su Salva per salvare le modifiche alla tabella.
Vincoli UNIQUE
Il vincolo UNIQUE può essere applicato a una colonna per garantire che non sia possibile
immettervi valori duplicati. Come i vincoli PRIMARY KEY, anche i vincoli UNIQUE
possono essere applicati a più colonne, come nell’esempio delle colonne FirstName e
LastName citato in precedenza. La principale differenza tra i due tipi di vincolo consiste
nella possibilità di configurare più vincoli UNIQUE in ogni tabella, mentre ognuna di queste
può contenere un solo vincolo PRIMARY KEY. I vincoli UNIQUE consentono anche agli
utenti di immettere un singolo valore NULL. I vincoli UNIQUE possono essere referenziati
da vincoli FOREIGN KEY. Non è possibile applicare un vincolo UNIQUE a una colonna
quando nella colonna di destinazione sono presenti valori duplicati.
Per creare un vincolo UNIQUE in una colonna di una tabella esistente, eseguire le seguenti
operazioni:
1. Aprire SSMS e connettersi all’istanza di database che ospita la tabella contenente la
colonna cui si desidera applicare il vincolo UNIQUE.
2. Espandere la cartella Database e localizzare il database che ospita la tabella contenente
la colonna cui si desidera applicare il vincolo UNIQUE.
3. Espandere il database appropriato e localizzare la tabella contenente la colonna cui si
desidera applicare il vincolo UNIQUE.
C0862254X.fm Page 431 Sunday, March 11, 2007 12:53 PM
Lezione 2: Esplicitazione dei vincoli impliciti
431
4. Fare clic con il pulsante destro del mouse su tabella e scegliere Modifica.
5. Fare clic con il pulsante destro del mouse sulla colonna cui si desidera applicare il
vincolo UNIQUE e scegliere Indici/Chiavi.
6. Nella finestra di dialogo Indici/Chiavi, fare clic su Aggiungi. Per impostazione
predefinita, viene creato un nuovo indice, ma questo viene modificato in un vincolo
UNIQUE.
7. Nell’area Generale del campo Colonne, selezionare la colonna cui si desidera applicare
il vincolo UNIQUE.
8. Nel campo Univoco, scegliere Sì.
9. In Tipo, scegliere Chiave univoca.
10. Nella casella Identità, modificare il nome immettendo Unique_ColumnName. Un
esempio è visibile nella figura 8-4.
11. Fare clic su Chiudi.
Figura 8-4
Configurazione di un vincolo UNIQUE
Vincoli FOREIGN KEY
In generale, i vincoli FOREIGN KEY vengono impiegati per collegare dati in due tabelle
separate, benché sia possibile usare un vincolo FOREIGN KEY per fare riferimento a
un’altra colonna nella stessa tabella. I vincoli FOREIGN KEY possono essere applicati a più
colonne e devono essere collegati a colonne cui è applicato un vincolo PRIMARY KEY o
UNIQUE.
C0862254X.fm Page 432 Sunday, March 11, 2007 12:53 PM
432
Capitolo 8
Progettazione dell’integrità dei dati
I vincoli FOREIGN KEY possono essere utilizzati per imporre l’integrità referenziale. in
questo caso, i dati nella tabella esterna sono protetti dall’eliminazione se questa operazione
rende orfani i dati nella tabella di riferimento. Ad esempio, nel database AdventureWorks vi
sono due tabelle, Sales.SalesPerson e Sales.SalesOrderHeader, collegate mediante un vincolo
FOREIGN KEY. La colonna SalesPersonID della tabella Sales.SalesOrderHeader è
collegata tramite un vincolo FOREIGN KEY alla colonna SalesPersonID della tabella
Sales.SalesPerson. L’integrità referenziale assicura che una riga di SalesPerson non possa
essere eliminata dalla tabella Sales.SalesPerson qualora una riga della tabella
Sales.SalesOrderHeader faccia riferimento al corrispondente ID di SalesPerson. Quando
SQL Server impone l’integrità referenziale, è possibile eliminare le righe di una tabella
destinazione di un vincolo FOREIGN KEY solo se i dati in tale riga non vengono usati
come riferimento nella tabella cui è stato applicato il vincolo FOREIGN KEY.
Nota
Limiti al numero di vincoli FOREIGN KEY
Nonostante SQL Server non preveda alcun limite predefinito al numero di vincoli FOREIGN
KEY che possono essere applicati a una tabella, questo valore è limitato dalla configurazione
hardware e dalla progettazione del database. Microsoft consiglia di un inserire più di 253 vincoli
FOREIGN KEY per tabella, e di fare in modo che una singola tabella non sia referenziata da
più di 253 vincoli FOREIGN KEY.
Per creare un vincolo FOREIGN KEY, eseguire le seguenti operazioni:
1. Aprire SSMS e connettersi all’istanza di database che ospita la tabella contenente la
colonna cui si desidera applicare il vincolo FOREIGN KEY.
2. Espandere la cartella Database e localizzare il database che ospita la tabella contenente
la colonna cui si desidera applicare il vincolo FOREIGN KEY.
3. Espandere il database appropriato e localizzare la tabella contenente la colonna cui si
desidera applicare il vincolo FOREIGN KEY.
4. Fare clic con il pulsante destro del mouse su tabella e scegliere Modifica.
5. Dal menu Progettazione tabelle, scegliere Relazioni.
6. Nella finestra di dialogo Relazioni chiavi esterne, fare clic su Aggiungi.
7. Fare clic su Specifica tabelle e colonne nella griglia a destra, quindi fare clic sul pulsante
puntini di sospensione (...) a destra della proprietà.
8. Nell’elenco a discesa Tabella chiave primaria della finestra di dialogo Tabelle e colonne,
selezionare la tabella che deve trovarsi sul lato della chiave principale o univoca della
relazione.
9. Nella griglia sotto la casella di testo Tabella chiave primaria, selezionare la colonna da
utilizzare come chiave primaria della tabella.
C0862254X.fm Page 433 Sunday, March 11, 2007 12:53 PM
Lezione 2: Esplicitazione dei vincoli impliciti
433
10. Nella cella adiacente della griglia, selezionare la colonna della tabella che utilizzerà la
chiave esterna, come mostrato nella figura 8-5.
Figura 8-5
Configurazione di un vincolo FOREIGN KEY
11. Fare clic su OK per creare la relazione, quindi fare clic su Chiudi per chiudere la
finestra di dialogo Relazioni chiavi esterne.
Esperienza reale
Orin Thomas
Quando l’autore di questo libro fece richiesta di rilascio della patente di guida, la
persona addetta all’immissione dei dati da stampare sul documento sbagliò
ripetutamente il nome del quartiere di residenza. Dopo il terzo errore, l’autore decise
di accettare lo sbaglio e sperare che al successivo rinnovo, dieci anni dopo, la persona
incaricata dell’operazione potesse correggere i dati errati. Tutto ciò non sarebbe
accaduto se il database delle patenti di guida avesse avuto un qualche tipo di vincolo
FOREIGN KEY in grado di impone l’integrità dei dati collegando il quartiere di
residenza all’elenco delle località presenti nello stato. Applicando un vincolo
FOREIGN KEY, nel documento sarebbe stato accettato solamente il nome di un
quartiere realmente esistente. Tuttavia, a quei tempi la tecnologia non era abbastanza
sofisticata e spesso chi scrive ha dovuto spiegare che alcune delle informazioni sul
documento di richiesta della patente di guida erano state trascritte in modo errato.
C0862254X.fm Page 434 Sunday, March 11, 2007 12:53 PM
434
Capitolo 8
Progettazione dell’integrità dei dati
Vincoli CHECK
I vincoli CHECK impongono l’integrità dei dati limitando i valori che gli utenti possono
immettere in una colonna. Per determinare se i dati sono validi, i vincoli CHECK applicano
un’espressione logica. I vincoli CHECK possono essere creati utilizzando qualsiasi
espressione logica che restituisca TRUE o FALSE mediante l’uso di operatori logici. Ad
esempio, per assicurarsi che i valori nella colonna delle temperature siano compresi tra -20 e
65, è possibile utilizzare la seguente espressione logica:
temperature >= -20 AND temperature <= 65
A una singola colonna possono essere applicati più vincoli CHECK. Inoltre, è possibile
anche applicare un singolo vincolo CHECK a più colonne di una tabella. Per fare ciò, è
necessario creare il vincolo CHECK a livello della tabella.
I vincoli CHECK presentano una relazione problematica con i valori NULL, poiché quando
vengono elaborati questi danno come risultato UNKNOWN. In presenza di una colonna di
tipo int cui è stato applicato un vincolo CHECK che richiede che il valore della colonna sia
uguale a 20 e nella colonna viene quindi inserito il valore NULL, il vincolo CHECK non
restituisce alcun errore, poiché non è in grado di elaborare il valore risultato.
Per creare un vincolo CHECK nella tabella, eseguire le seguenti operazioni:
1. Aprire SSMS e connettersi all’istanza di database che ospita la tabella contenente la
colonna cui si desidera applicare il vincolo CHECK.
2. Espandere la cartella Database e localizzare il database che ospita la tabella contenente
la colonna cui si desidera applicare il vincolo CHECK.
3. Espandere il database appropriato e localizzare la tabella contenente la colonna cui si
desidera applicare il vincolo CHECK.
4. Fare clic con il pulsante destro del mouse su tabella e scegliere Modifica.
5. Fare clic con il pulsante destro del mouse sulla colonna cui si desidera applicare il
vincolo CHECK e scegliere Verifica vincoli. Viene visualizzata la finestra di dialogo
Verifica vincoli mostrata nella figura 8-6.
6. Fare clic su Aggiungi per aggiungere un vincolo.
7. Nel campo Espressione della griglia, immettere l’espressione SQL del vincolo
CHECK. Per ulteriori informazioni sulla creazione di espressioni SQL per i vincoli
CHECK, vedere la casella Maggiori informazioni.
8. Fare clic su Chiudi.
C0862254X.fm Page 435 Sunday, March 11, 2007 12:53 PM
Lezione 2: Esplicitazione dei vincoli impliciti
Figura 8-6
435
Aggiunta di un vincolo CHECK
Maggiori informazioni
Creazione di vincoli CHECK
Per ulteriori informazioni su come creare un vincolo CHECK, consultare l’articolo MSDN
all’indirizzo msdn2.microsoft.com/en-us/library/ms191245(SQL.90,d=ide).aspx.
Definizioni DEFAULT
In SQL Server 2005, ciascuna colonna di un record deve contenere un valore, anche se NULL.
Se una colonna non consente l’immissione di valori NULL, è necessario specificare in modo
esplicito un valore; in caso contrario, il motore database restituisce un errore. Dal momento
che in alcuni casi il valore NULL non può essere accettato e che a volte può essere necessario
caricare una riga di dati in una tabella quando non si conoscono ancora alcuni dei valori della
colonna, alla colonna può essere applicata una definizione DEFAULT. Durante
l’inserimento di una riga in una tabella, se non si forniscono i dati per la colonna cui viene
applicata la definizione DEFAULT, SQL Server inserisce automaticamente il valore indicato
nella definizione DEFAULT.
Nota
Definizioni di valori predefiniti comuni
È pratica comune specificare il valore zero come definizione DEFAULT per le colonne
numeriche, mentre N/D viene usato per le colonne stringa.
Per creare una definizione DEFAULT per una colonna di una tabella, eseguire le seguenti
operazioni:
1. Aprire SSMS e connettersi all’istanza di database che ospita la tabella contenente la
colonna cui si desidera applicare la definizione DEFAULT.
C0862254X.fm Page 436 Sunday, March 11, 2007 12:53 PM
436
Capitolo 8
Progettazione dell’integrità dei dati
2. Espandere la cartella Database e localizzare il database che ospita la tabella contenente
la colonna cui si desidera applicare la definizione DEFAULT.
3. Espandere il database appropriato e localizzare la tabella contenente la colonna cui si
desidera applicare la definizione DEFAULT.
4. Fare clic con il pulsante destro del mouse su tabella e scegliere Modifica.
5. Selezionare la colonna per cui si desidera specificare il valore predefinito.
6. Nella scheda Proprietà colonna, immettere il nuovo valore predefinito nella proprietà
del valore predefinito dell’associazione, come mostrato nella figura 8-7.
Figura 8-7 Impostazione del valore predefinito Victoria nella colonna State
Come consentire l’uso di valori NULL
In talune circostanze, può essere necessario configurare una colonna in modo da consentire
l’immissione di valori NULL. È importante ricordare che un valore NULL è differente dal
valore zero o da un carattere di lunghezza zero come“”. Valore NULL significa che nella
colonna non è stato immesso nulla. SQL Server immette il valore NULL quando l’utente
inserisce una riga senza includervi un valore per una specifica colonna, a condizione che i valori
NULL siano consentiti e che non esista alcuna definizione DEFAULT.
L’ammissione di valori NULL costituisce una limitazione, poiché alle colonne contenenti
valori NULL non possono essere applicati i vincoli PRIMARY KEY. Qualora in una
colonna siano presenti più valori NULL, non possono esserle applicati vincoli con chiavi di
tipo UNIQUE. Da ciò deriva anche che se in una colonna sono presenti più valori NULL,
essa non può essere la destinazione di un vincolo FOREIGN KEY.
Per impostazione predefinita, le colonne permettono l’immissione di valori NULL. Per
modificare l’opzione NULL di una colonna, eseguire le seguenti operazioni:
1. Aprire SSMS e accedere al database contenente la tabella con la colonna da modificare.
C0862254X.fm Page 437 Sunday, March 11, 2007 12:53 PM
Lezione 2: Esplicitazione dei vincoli impliciti
437
2. Fare clic con il pulsante destro del mouse sulla tabella appropriata in Esplora oggetti e
scegliere Modifica.
3. Selezionare la colonna appropriata e deselezionare la casella di controllo Consenti
valori NULL come mostrato nella figura 8-8.
Figura 8-8
Configurazione del supporto di valori NULL
La proprietà di una colonna esistente può essere modificata solamente se al suo interno
non sono presenti valori NULL e sulla colonna stessa non è definito alcun indice. Per
impedire l’immissione di valori NULL in una colonna esistente e contenente valori NULL,
eseguire le seguenti operazioni:
1. Creare una nuova colonna che utilizzi una definizione DEFAULT per inserire un
valore appropriato.
2. Copiare i dati dalla colonna esistente alla nuova colonna.
3. Eliminare la vecchia colonna.
4. Rinominare la nuova colonna con il nome della vecchia.
Verifica rapida
1. Quale tipo di vincolo è necessario utilizzare in una tabella per assicurarsi che gli
utenti possano inserirvi solo valori numerici compresi in un particolare
intervallo?
2. In che modo è possibile accertarsi che il valore 0 sia applicato a una colonna di
tipo int se l’istruzione INSERT non specifica alcun valore per la colonna in
questione?
3. Quale è il numero massimo di valori NULL che possono esistere in una colonna
cui si desidera applicare un vincolo UNIQUE?
Risposta della verifica rapida
1. A tal fine, è necessario utilizzare il vincolo CHECK.
2. Utilizzare la definizione DEFAULT.
3. In questa situazione il numero massimo è pari a uno.
C0862254X.fm Page 438 Sunday, March 11, 2007 12:53 PM
438
Capitolo 8
Progettazione dell’integrità dei dati
Trigger
I trigger DML possono operare sulle istruzioni UPDATE, INSERT e DELETE, e possono
essere configurati in modo da imporre regole aziendali e integrità dei dati quando gli utenti
modificano i dati contenuti in tabelle o viste. I trigger DML possono essere creati, modificati
e cancellati mediante la sintassi di Transact-SQL. Inoltre, essi permettono di eseguire le
seguenti funzioni:
■
Applicare le modifiche a cascata sulle tabelle correlate nel database.
■
Proteggere da operazioni INSERT, UPDATE e DELETE errate e imporre
l’applicazione di limitazioni più complesse di quelle definibili mediante i vincoli
CHECK.
■
Permettere la valutazione dello stato di una tabella prima o dopo della modifica dei dati
e utilizzare tale valutazione per dare inizio alle operazioni successive.
■
Più trigger dello stesso tipo (INSERT, UPDATE o DELETE) applicati a una tabella
consentono l’esecuzione di operazioni differenti in base al contenuto dell’istruzione
che avvia i trigger stessi.
Esistono tre tipi di trigger DML, AFTER, INSTEAD OF e CLR.
Trigger AFTER
SQL Server esegue i trigger AFTER dopo avere eseguito l’operazione contenuta in
un’istruzione INSERT, UPDATE o DELETE. I trigger AFTER possono essere specificati
solamente all’interno di tabelle.
Trigger INSTEAD OF
SQL Server esegue i trigger INSTEAD OF al posto delle normali operazioni legati ai trigger.
Invece di far sì che SQL Ser ver inserisca una nuova rig a nella tabella come
nell’esempio precedente, il trigger INSTEAD OF viene avviato prima che SQL Server
apporti qualsiasi modifica basata sull’istruzione di avvio del trigger. Ad esempio, un trigger
INSTEAD OF può venire avviato se viene rilevata un’istruzione DELETE. Invece di
eliminare i dati dalla tabella, il trigger INSTEAD OF esegue un’altra operazione. I trigger
INSTEAD OF vengono anche usati spesso per estendere i tipi di aggiornamenti supportati
da una vista. Ad esempio, i trigger INSTEAD OF possono essere configurati per modificare
più tabelle base contenenti colonne che utilizzano il tipo di dati timestamp.
Trigger CLR
Un trigger CLR (Common Language Runtime) può essere sia un trigger AFTER, sia un
trigger INSTEAD OF. I trigger CLR eseguono metodi scritti in codice gestito. Questi trigger
possono essere impiegati quando si rende necessario avviare operazioni più complesse di
quelle ottenibili mediante Transact-SQL.
C0862254X.fm Page 439 Sunday, March 11, 2007 12:53 PM
Lezione 2: Esplicitazione dei vincoli impliciti
Maggiori informazioni
439
Creazione dei trigger
La creazione di un trigger è un processo molto particolareggiato. Ulteriori informazioni sulla
creazione dei trigger sono disponibili nell’articolo MSDN all’indirizzo msdn.microsoft.com/
library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp.
Suggerimenti d’esame
Nonostante a volte non appaia evidente, le domande di un esame di
certificazione non sono scritte per essere simili a indovinelli subdoli. Lo scopo di ciascuna
domanda di esame è di verificare la comprensione acquisita di un dato concetto. Infatti, la
risposta risulta ovvia per chi abbia compreso a fondo i concetti alla base di ogni domanda. A
volte per avere una visione più chiara sul quesito posto è possibile chiedersi “Che cosa
chiede in realtà questa domanda?” Ciò che può a prima vista apparire una domanda relativa
all’integrità dei dati può in realtà risultare semplicemente qualcosa del tipo “Avete capito le
differenze tra i vari tipi di vincoli?”
Esperienza reale
Orin Thomas
In passato, le persone ordinavano i prodotti parlando al telefono con un venditore. Uno
dei vantaggi di questo sistema era la presenza di un livello di controllo degli errori operante
da persona a persona. Con le odierne comunicazioni tra aziende, se qualcuno non presta
la massima attenzione è possibile ordinare un quantitativo di merce molto superiore a
quello desiderato. In passato, quando una persona diceva al venditore “Desidero ricevere
20.000 rotoli di carta igienica”, il venditore rispondeva sicuramente “È sicuro di volerne
ordinare così tanta?” La vendita non sarebbe stata completata fino a quando il venditore
non fosse stato convinto che 20.000 rotoli erano esattamente ciò che il cliente voleva.
Con i moderni sistemi a elevata automazione disponibili oggi, in assenza di un qualche
tipo di procedura di controllo degli errori un tale ordine può venire semplicemente
confermato, con i relativi costi addebitati automaticamente al cliente. Se il cliente che
effettua l’ordine deve concludere centinaia di transazioni ogni settimana, egli può non
accorgersi dell’errore fino all’arrivo di un camion con il conducente che chiede “Dove
devo scaricare questa merce?”
ESERCIZIO PRATICO
Configurazione di un vincolo CHECK
In questo esercizio pratico viene applicato un vincolo CHECK alla colonna PostalCode nella
tabella Person.Address del database AdventureWorks. Questo vincolo CHECK consente agli
utenti di immettere nella colonna solamente codici di avviamento postale di cinque cifre.
C0862254X.fm Page 440 Sunday, March 11, 2007 12:53 PM
440
Capitolo 8
Progettazione dell’integrità dei dati
1. Aprire SSMS e connettersi all’istanza locale.
2. Espandere la cartella Database.
3. Espandere il database AdventureWorks.
4. Espandere la cartella Tabelle.
5. Fare clic con il pulsante destro del mouse su tabella Person.Address e scegliere
Modifica.
6. Fare clic con il pulsante destro del mouse sulla colonna PostalCode e scegliere Verifica
vincoli.
7. Fare clic su Aggiungi.
8. Nel campo Espressione della griglia, immettere
PostalCode LIKE ‘[0-9][0-9][0-9][0-9][0-9]’.
9. nel campo Verifica dati esistenti durante la creazione o la riattivazione, scegliere No
dall’elenco a discesa. Ciò è necessario poiché i dati esistenti nella tabella contengono
voci che violano il vincolo CHECK.
10. Fare clic su Chiudi. Nella barra degli strumenti, fare clic su Salva per salvare le modifiche
nella tabella Person.Address.
Riepilogo della lezione
■
I vincoli possono essere impiegati per imporre l’integrità dei dati.
■
Un vincolo PRIMARY KEY può comprendere più colonne e imporre l’univocità, ma
non consente l’immissione di valori NULL.
■
Un vincolo UNIQUE può essere applicato a una singola colonna e consente
l’immissione di un solo valore NULL.
■
Un vincolo FOREIGN KEY consente solo valori già presenti nella colonna di
destinazione. La colonna di destinazione può trovarsi nella stessa tabella o in una
differente, ma in entrambi i casi deve esserle applicato un vincolo PRIMARY KEY o
UNIQUE.
■
Un vincolo CHECK elabora i dati immessi confrontandoli con un’istruzione logica.
■
Una definizione DEFAULT imposta il valore predefinito per la colonna (in assenza di
altri dati immessi).
■
Un trigger AFTER avvia un’operazione in seguito all’esecuzione di una modifica in
una transazione.
■
Un trigger INSTEAD OF esegue un operazione invece di consentire l’esecuzione di
una modifica nella transazione.
C0862254X.fm Page 441 Sunday, March 11, 2007 12:53 PM
Lezione 2: Esplicitazione dei vincoli impliciti
441
Revisione della lezione
È possibile utilizzare le seguenti domande per verificare la propria conoscenza delle
informazioni presentate nella lezione 2, “Esplicitazione dei vincoli impliciti”. Qualora si
preferisca eseguire la verifica in formato elettronico, tali domande sono disponibili anche nel
CD allegato.
Nota
Risposte
Le risposte alle domande e le relative spiegazioni sono incluse nella sezione “Risposte” nella
parte conclusiva del libro.
1. Si desidera assicurarsi che nessun appaltatore possa immettere più di 60 ore settimanali
nella colonna Weekly_Hours della tabella Worksheet. Quale dei seguenti vincoli è
necessario applicare?
A. UNIQUE
B. FOREIGN KEY
C. PRIMARY KEY
D. CHECK
2. Qualora non venga immesso alcun altro valore, si desidera assicurarsi che nella colonna
Hours della tabella Worksheet sia immesso il valore 0. Quale delle seguenti tecnologie
può essere utilizzata per ottenere questo risultato?
A. Supporto di valori Null
B. Vincolo CHECK
C. Definizione DEFAULT
D. Vincolo FOREIGN KEY
3. Si desidera assicurarsi che nella colonna Contractor_Name della tabella Worksheet
possano essere immessi solamente i nomi presenti nell’elenco dei 500 appaltatori
approvati contenuto nella tabella Contractors. Quale tecnologia può essere utilizzata
per ottenere questo risultato?
A. vincolo FOREIGN KEY
B. Vincolo UNIQUE
C. Trigger INSTEAD OF
D. Trigger AFTER
C0862254X.fm Page 442 Sunday, March 11, 2007 12:53 PM
442
Capitolo 8
Progettazione dell’integrità dei dati
Lezione 3: Assegnazione dei tipi di dati per il controllo delle
caratteristiche dei dati memorizzati in una colonna
Le caratteristiche del modo in cui SQL Server memorizza i dati all’interno di una colonna
possono essere estremamente importanti, in particolar modo per i dati numerici. Nelle
applicazioni di tipo ingegneristico o scientifico, la precisione di una misurazione può rivelarsi
di fondamentale importanza. Esiste infatti una grande differenza tra un valore preciso al
millimetro e uno preciso al decimo di millimetro. Se i tipi di dati non vengono configurati
correttamente, SQL Server può modificare i dati memorizzati in un database rendendoli
apparentemente più o meno precisi di quanto non siano in realtà. Una colonna di una tabella
che arrotonda automaticamente una cifra decimale sui dati immessi può causare seri
problemi e persino richiedere la ripetizione di tutte le misurazioni.
Al termine di questa lezione, si sarà in grado di:
■
Assegnare i tipi di dati di Transact-SQL per il controllo delle caratteristiche dei dati
memorizzati in una colonna.
■
Utilizzare tipi di dati Alias per garantire che le colonne condividano lunghezza, tipo e
supporto dei valori NULL.
Tempo previsto per la lezione: 30 minuti
Tipi di dati di Transact-SQL
A volte, per garantire l’integrità dei dati può essere sufficiente verificare che alla colonna
appropriata sia assegnato uno dei tipi di dati esistenti di Transact-SQL. Per assicurarsi che
nella colonna usata per memorizzare i numeri di telefono gli utenti possano inserire
solamente numeri ma non testo, è necessario verificare che la colonna impieghi il tipo di dati
int invece del tipo varchar. Esistono 28 tipi di dati base di Transact-SQL disponibili in SQL
Server 2005, tutti elencati nella tabella 8-1.
Tabella 8-1
Descrizione dei tipi di dati di Transact-SQL
Tipo di dati
Descrizione
bigint
Numero intero compreso tra -2^63 e (2^63)-1.
int
Numero intero compreso tra -2^31 e (2^31)-1.
smallint
Numero intero compreso tra -2^15 e (2^15)-1.
tinyint
Da 0 a 255.
bit
0, 1 o NULL.
C0862254X.fm Page 443 Sunday, March 11, 2007 12:53 PM
Lezione 3: Assegnazione dei tipi di dati per il controllo delle caratteristiche dei dati memorizzati in una colonna
Tabella 8-1
443
Descrizione dei tipi di dati di Transact-SQL (continua)
Tipo di dati
Descrizione
decimal
Fino a 38 cifre possono essere memorizzate a sinistra e/o a destra della
virgola decimale.
numeric
Dal punto di vista funzionale, equivale al tipo decimal.
money
Da -922.337.203.685.477,5808 a 922.337.203.685.477,5807.
smallmoney
Da -214.748,3648 a 214.748,3647.
float
Dati numerici a virgola mobile nell’intervallo compreso tra -1,79E+308 e
-2,23E-308, 0 e da 2,23E-308 a 1,79E+308.
real
Dati numerici a virgola mobile nell’intervallo compreso tra -3,40E+38
e -1,18E-38, 0 e da 1,18E-38 a 3,40E+38.
datetime
Da 1 gennaio 1753 a 31 dicembre 9999. Precisione di 3,33 millisecondi.
smalldatetime
Da 1 gennaio 1900 a 6 giugno 2079. Precisione di un minuto.
char
Dati carattere non Unicode a lunghezza fissa, con lunghezza pari a n
byte, dove n è un valore compreso tra 1 e 8000.
varchar
Dati carattere non Unicode a lunghezza variabile, con lunghezza pari a
n byte, dove n è un valore compreso tra 1 e 8000.
text
Dati carattere non Unicode a lunghezza variabile della code page del
server, con lunghezza massima di 2^31-1 (2.147.483.647) caratteri.
nchar
Dati carattere Unicode a lunghezza fissa, con lunghezza di n caratteri. n
deve essere un valore compreso tra 1 e 4000.
nvarchar
Dati carattere Unicode a lunghezza variabile. n può essere un valore
compreso tra 1 e 4000.
ntext
Dati carattere Unicode a lunghezza variabile, con lunghezza massima di
2^30-1 (1.073.741.823) caratteri.
binary
Dati binari a lunghezza fissa, con lunghezza di n byte, dove n è un valore
compreso tra 1 e 8000.
varbinary
Dati binari a lunghezza variabile, con lunghezza di n byte, dove n è un
valore compreso tra 1 e 8000.
image
Dati binari a lunghezza variabile, con lunghezza compresa tra 0 e 2^31-1
(2.147.483.647) byte.
C0862254X.fm Page 444 Sunday, March 11, 2007 12:53 PM
444
Capitolo 8
Progettazione dell’integrità dei dati
Tabella 8-1
Descrizione dei tipi di dati di Transact-SQL (continua)
Tipo di dati
Descrizione
cursor
Tipo di dati per parametri OUTPUT di variabili o stored procedure
contenenti un riferimento a un cursore.
sql_variant
Tipo di dati contenente valori di vari tipi di dati supportati da SQL
Server 2005, a esclusione di text, ntext, image, timestamp e sql_variant.
table
Tipo di dati speciale utilizzabile per memorizzare un insieme di risultati
da sottoporre a elaborazioni successive. Il tipo di dati table è destinato
principalmente alla memorizzazione temporanea di un insieme di righe
restituite come risultato di una funzione valutata a livello di tabella.
timestamp
Usato generalmente come meccanismo per l’assegnazione della
versione alle righe di una tabella. Viene incrementato a ciascuna
operazione di immissione o aggiornamento eseguita sulla tabella
contenente una colonna timestamp all’interno di un database.
uniqueidentifier
GUID a 16 byte.
xml
Usato per memorizzare dati in formato XML. Nuovo tipo di SQL
Server 2005. Il contenuto può essere limitato a frammenti o documenti
XML di formato corretto. È possibile anche specificare un insieme di
schemi XML.
Nota
Utilizzo di smallint al posto di int o bigint
I tipi di dati quali smallint e tinyint possono essere impiegati come mezzo per ottimizzare la
memorizzazione dei dati all’interno di extent. Questi tipi riservano uno spazio dati minore e
aumentano l’efficienza del database. Ulteriori informazioni sugli extent sono disponibili nel
capitolo 4, “Ripristino di emergenza”.
Tipi di dati Alias
I tipi Alias sono basati su tipi di dati di sistema. I tipi alias vengono utilizzati quando più
tabelle all’interno di un database devono contenere uno stesso tipo di dati in una colonna ed
è necessario assicurarsi che tali colonne abbiano impostazioni identiche per tipo di dati,
lunghezza e supporto di valori NULL. Ad esempio, può accadere di operare su dati
scientifici e di dover garantire che tutte le tabelle all’interno del database possano
memorizzare le misurazioni utilizzando uno stesso livello di precisione. Il modo più
C0862254X.fm Page 445 Sunday, March 11, 2007 12:53 PM
Lezione 3: Assegnazione dei tipi di dati per il controllo delle caratteristiche dei dati memorizzati in una colonna
445
semplice per ottenere questo risultato consiste nel creare un tipo alias appropriato da
applicare a tutte le colonne in cui devono essere memorizzate le misurazioni scientifiche da
inserire nel database.
Creando un tipo alias nel database modello, SQL Server lo copia in tutti i database creati
dagli utenti. Creando un tipo alias all’interno di un database definito dall’utente, tale tipo
esiste solamente all’interno del database in questione. Durante la creazione di un tipo alias,
è necessario specificare i seguenti parametri:
■
Nome
■
Supporto di valori Null
■
Tipo di dati di sistema su cui si basa il nuovo tipo di dati
Per creare un tipo di dati chiamato Sci_Measure simile a quello citato nell’esempio
precedente, è necessario eseguire la seguente istruzione Transact-SQL:
CREATE TYPE Sci_Measure
FROM decimal (6,3)
NOT NULL
;
Maggiori informazioni
Creazione dei tipi
Per ulteriori informazioni sulla creazione di tipi alias, consultare l’articolo MSDN all’indirizzo
msdn2.microsoft.com/en-us/library/ms175007(SQL.90,d=ide).aspx.
Verifica rapida
1. Quale è l’utilità dei tipi di dati?
2. Quale tipo di dati di Transact-SQL è necessario utilizzare in una colonna in cui si
desidera memorizzare solo i valori 0, 1 o NULL?
3. Quale tipo di dati di Transact-SQL equivale dal punto di vista funzionale al tipo
di dati decimal?
Risposta della verifica rapida
1. I tipi alias possono essere impiegati per garantire che un insieme di colonne abbia
impostazioni identiche per tipo di dati, lunghezza e supporto di valori NULL.
2. Per ottenere questo scopo può essere usato il tipo di dati bin.
3. Dal punto di visto funzionale, il tipo di dati numeric equivale al tipo di dati
decimal.
C0862254X.fm Page 446 Sunday, March 11, 2007 12:53 PM
446
Capitolo 8
Progettazione dell’integrità dei dati
Esperienza reale
Orin Thomas
Tutti i lettori hanno sicuramente sentito raccontare la storia del tale che trovò il modo
di trasferire sul proprio tutti gli errori di arrotondamento nelle transazioni finanziarie
quotidiane di una banca. Questo aneddoto è un ottimo esempio del perché è così
importante essere il più precisi possibile durante l’impostazione dei tipi di dati. Come
quella persona ebbe modo di scoprire, con milioni di transazioni eseguite, anche un
errore di arrotondamento di una piccolissima frazione di centesimo riesce a
trasformarsi in una quantità di denaro davvero ingente. Nel calcolo scientifico, è
possibile essere tanto precisi solo quanto consentito dalla misurazione più imprecisa
eseguita. Tuttavia, ciò può anche dare luogo a risultati inversi. Se si ritiene che i propri
dati siano più precisi di quanto non siano in realtà, durante il data mining è possibile
rilevare cose che in realtà non sono presenti. In molte applicazioni reali, è necessario
registrare i dati nel modo più preciso possibile.
Tipi CLR definiti dall’utente
SQL Server 2005 introduce i tipi CLR (Common Language Runtime) definiti dall’utente
(UDT, Used Defined Types). I tipi UDT consentono di estendere il sistema di tipo scalare
del server, oltre a permettere di memorizzare gli oggetti CLR all’interno di un database SQL
Server 2005. I tipi UDT possono contenere più elementi. Ad esempio, un singolo tipo UDT
può contenere dati di tipo numerical e image. Ciò distingue i tipi UDT dai tipi di dati alias, i
quali sono limitati a essere una variazione di un tipo di dati SQL Server base. I tipi UDT sono
i più adatti alla memorizzazione dei seguenti tipi di dati:
■
Tipi date, time, currency ed extended numeric
■
Applicazioni geospaziali
■
Dati codificati o crittografati
La creazione di tipi UDT all’interno di SQL Server 2005 consiste nelle seguenti operazioni:
I tipi UDT vengono
creati utilizzando qualsiasi linguaggio supportato da Microsoft .NET Framework CLR.
I dati vengono esposti come campi e proprietà di una classe o struttura .NET
Framework. I comportamenti sono definiti mediante metodi della classe o struttura.
■
Codifica e generazione dell’assembly che definisce il tipo UDT.
■
Registrazione degli assembly.
■
Creare il tipo UDT in SQL Server. Una volta registrato l’assembly, utilizzare l’istruzione
Utilizzare l’istruzione CREATE ASSEMBLY per
copiare l’assembly nel database.
CREATE TYPE per creare il tipo UDT.
■
Creazione di tabelle, variabili o parametri mediante un tipo UDT.
UDT come parte della definizione di colonna per una tabella.
Utilizzare il tipo
C0862254X.fm Page 447 Sunday, March 11, 2007 12:53 PM
Lezione 3: Assegnazione dei tipi di dati per il controllo delle caratteristiche dei dati memorizzati in una colonna
447
A causa della loro natura complessa, ai fine dell’esame il lettore deve essere a conoscenza
dell’esistenza dei tipi UDT, benché nell’ambito dell’esame stesso non sia previsto di doverne
creare personalmente.
Nota
Esecuzione dei tipi CLR
Per impostazione predefinita, la capacità di eseguire codice CLR all’interno di SQL Server
2005 è disabilitata. L’esecuzione del codice CLR può essere abilitata tramite la stored
procedure di sistema sp_configure.
Procedure consigliate
Utilizzo di tipi UDT
Ai tipi UDT è possibile accedere da sistema nell’insieme, ma un loro uso frequente può
causare il degrado delle prestazioni.
ESERCIZIO PRATICO
Creazione di un tipo di dati Alias
In questo esercizio pratico, nel database AdventureWorks viene creato un nuovo tipo di dati
alias, quindi viene verificato che sia possibile applicarlo alle colonne esistenti (se necessario).
1. Utilizzare SSMS per connettersi all’istanza locale.
2. Espandere la cartella Database e visualizzare i database installati sull’istanza locale.
3. Fare clic con il pulsante destro del mouse sul database AdventureWorks e scegliere
Nuova query.
4. Nella finestra di query, immettere la seguente istruzione Transact-SQL:
CREATE TYPE Sci_Measure
FROM decimal (8,3)
NOT NULL ;
5. Fare clic sul pulsante Esegui nella barra degli strumenti.
6. Nel riquadro Messaggi, verificare che sia visualizzato il messaggio Il comando o i
comandi sono stati completati.
7. Espandere il database AdventureWorks.
8. Espandere la cartella Tabelle.
9. Fare clic con il pulsante destro del mouse sulla tabella Person.Address e scegliere
Modifica.
10. Selezionare la colonna StateProvinceID e utilizzare l’elenco a discesa Tipo di dati nella
scheda Proprietà colonna per verificare che sia possibile modificare il tipo di dati della
colonna selezionando Sci_Measure.
11. Riportare la colonna StateProvinceID al suo valore int originale e chiudere la finestra
Tabelle.
C0862254X.fm Page 448 Sunday, March 11, 2007 12:53 PM
448
Capitolo 8
Progettazione dell’integrità dei dati
Riepilogo della lezione
■
I 28 tipi di dati base di Transact-SQL possono essere impiegati per garantire che gli
utenti possano immettere in una colonna solamente uno specifico tipo di dati.
■
Il tipo di dati alias è una versione personalizzata di uno dei 28 tipi di dati base di
Transact-SQL.
■
I tipi di dati alias vengono spesso impiegati per standardizzare la formattazione dei dati
tra più tabelle di un database.
■
I tipi di dati definiti dall’utente (UDT, User Defined Types) consentono di estendere il
sistema di tipo scalare e permettono di memorizzare oggetti CLR all’interno di un
database SQL Server 2005.
■
I tipi UDT possono contenere più elementi.
Revisione della lezione
È possibile utilizzare le seguenti domande per verificare la propria conoscenza delle
informazioni presentate nella lezione 3, “Assegnazione dei tipi di dati per il controllo delle
caratteristiche dei dati memorizzati in una colonna”. Qualora si preferisca eseguire la verifica
in formato elettronico, tali domande sono disponibili anche nel CD allegato.
Nota
Risposte
Le risposte alle domande e le relative spiegazioni sono incluse nella sezione “Risposte” nella
parte conclusiva del libro.
1. Si desidera accertare che tutte le misurazioni registrate nelle tabelle geologica,
mineralogica e paleontologica siano memorizzate con lo stesso grado di precisione.
Come è possibile fare in modo che ciò avvenga?
A. Creando un tipo di dati alias.
B. Utilizzando il tipo di dati varchar.
C. Utilizzando il tipo di dati char.
D. Utilizzando il tipo di dati nchar.
2. Quale dei seguenti tipi di dati può essere necessario memorizzare utilizzando un tipo
UDT? (Scegliere tutte le risposte corrette.)
A. Codici di avviamento postale
B. Dati crittografati
C. Dati geospaziali
D. Compleanni
C0862254X.fm Page 449 Sunday, March 11, 2007 12:53 PM
Revisione del Capitolo 8
449
Revisione del capitolo
Per provare e affinare ulteriormente le abilità acquisite nel presente capitolo, è possibile
eseguire le seguenti attività:
■
Rivedere il riepilogo del capitolo.
■
Rivedere l’elenco delle parole chiave introdotte nel capitolo.
■
Completare lo scenario possibile. Questi scenari definiscono situazioni reali che si
riferiscono agli argomenti del capitolo e richiedono di trovare una soluzione.
■
Completare gli esercizi proposti.
■
Eseguire una prova pratica.
Riepilogo del capitolo
■
I conflitti si verificano in occasione di più aggiornamenti contemporanei agli stessi
dati ubicati su sottoscrittori differenti e quando tali sottoscrittori eseguono quindi la
sincronizzazione con il server di replica. Questi conflitti possono essere risolti
applicando le norme di risoluzione dei conflitti.
■
I vincoli PRIMARY KEY possono comprendere più colonne e imporre l’univocità,
ma non consentono l’immissione di valori NULL. A una singola tabella possono
essere applicati più vincoli UNIQUE. I vincoli FOREIGN KEY consentono solo i
valori già esistenti nella colonna di destinazione. I vincoli CHECK elaborano i dati
immessi confrontandoli con un’istruzione logica. Le definizioni DEFAULT
permettono di impostare il valore predefinito di una colonna (in assenza di altri dati
immessi).
■
Un trigger AFTER avvia un’operazione in seguito all’esecuzione di una modifica in
una transazione. Un trigger INSTEAD OF esegue un operazione invece di consentire
l’esecuzione di una modifica nella transazione.
■
I 28 tipi di dati base di Transact-SQL possono essere impiegati per garantire che in
una colonna possano essere immessi solamente specifici tipi di dati. Un tipo di dati
alias è una versione personalizzata di uno dei 28 tipi base e viene impiegato per
standardizzare la formattazione dei dati tra più tabelle di un database.
■
I tipi di dati definiti dall’utente (UDT, User Defined Types) consentono di estendere
il sistema di tipo scalare, possono contenere più elementi e permettono di
memorizzare oggetti CLR.
C0862254X.fm Page 450 Sunday, March 11, 2007 12:53 PM
450
Revisione del Capitolo 8
Termini chiave
Se non si conosce il significato dei seguenti termini chiave, è possibile controllare le proprie
risposte consultando il glossario inserito nella parte conclusiva del manuale.
■
trigger AFTER
■
tipo di dati alias
■
articolo
■
vincolo CHECK
■
norma di risoluzione dei conflitti
■
definizione DEFAULT
■
vincolo FOREIGN KEY
■
trigger INSTEAD OF
■
supporto di valori Null
■
vincolo PRIMARY KEY
■
server di pubblicazione
■
agente di lettura coda
■
sottoscrittore
■
tipo di dati di Transact-SQL
■
vincolo UNIQUE
■
tipo di dati definito dall’utente
Scenari possibili
Nei seguenti scenari possibili viene messo in pratica quanto appreso sulla definizione dei
vincoli e l’imposizione dell’integrità dei dati. Per trovare le risposte alle domande seguenti,
consultare la sezione relativa alle risposte al termine del manuale.
Scenario possibile 1: Esplicitazione dei vincoli impliciti
La società Contoso fornisce il supporto IT per il reparto di assegnazione e registrazione delle
automobili. Il reparto ha 20 uffici in aree metropolitane e regionali. L’ufficio CBD (Central
Business District) ha un computer SQL Server 2005 che opera come server di replica.
Ciascuno dei 19 uffici dispone di un computer SQL Server 2005 configurato come
sottoscrittore.
C0862254X.fm Page 451 Sunday, March 11, 2007 12:53 PM
Revisione del Capitolo 8
451
1. Il reparto desidera assicurarsi che in caso di conflitto abbia la priorità il computer SQL
Server 2005 presso CBD. Quale norma di risoluzione dei conflitti è necessario
implementare?
2. Il reparto desidera verificare che nella tabella drivers_license possano essere immesse
solamente le località approvate scelte dall’apposita tabella. Come è possibile ottenere
questo risultato?
3. Il reparto ha la necessità di accertarsi che i dati della targa immessi nel database
contengano tre lettere seguite da tre numeri. Come è possibile ottenere questo
risultato?
Scenario possibile 2: Tipi di dati
Nel proprio database di inventario, la società Tailspin Toys ha in corso la definizione di una
tabella destinata a contenere le informazioni relative alla rete di vendita. In questa tabella
saranno memorizzate informazioni quali l’indirizzo della scheda Ethernet, il software
installato su ciascun computer e le informazioni relative alle licenze d’uso.
1. Quale tipo di dati di Transact-SQL è più adatto all’archiviazione dei numeri degli
indirizzi IPv4 ?
2. Quale tipo di dati di Transact-SQL è più adatto all’archiviazione di indirizzi esadecimali
MAC a 12 cifre delle schede Ethernet?
3. Un’utilità di inventario scrive la configurazione software di ogni computer in un file
XML formattato in modo coerente. Quale tipo di dati di Transact-SQL è necessario
applicare alla colonna utilizzata per archiviare tali file?
Operazioni consigliate
Per semplificare il raggiungimento degli obiettivi dell’esame presentato nel capitolo,
completare le seguenti attività:
Progettazione dell’integrità dei dati
■
Esercizio pratico 1: Utilizzare una norma di risoluzione dei conflitti. Creare un nuovo
database sul server Melbourne e configurarlo come server di replica. Assegnare la
norma di risoluzione dei conflitti che assegna la precedenza alle modifiche effettuate
sul sottoscrittore.
■
Esercizio pratico 2: Applicare vincoli CHECK. Nel nuovo database creato sul server
Melbourne nell’esercizio 1, creare una tabella. Configurare un vincolo CHECK su una
riga di questa tabella per fare in modo che non siano accettati valori superiori a 60.
C0862254X.fm Page 452 Sunday, March 11, 2007 12:53 PM
452
Revisione del Capitolo 8
■
Esercizio pratico 3: Applicare tipi di dati di Transact-SQL. Creare tre nuove righe nella
tabella creata nell’esercizio 2. Utilizzare i tipi di dati di Transact-SQL per garantire che
nella prima riga sia possibile immettere solo i valori 0, 1 o NULL, che nella seconda
riga sia possibile immettere solamente date comprese tra il primo gennaio 1900 e il 6
giugno 2079 e infine che nella terza riga sia possibile immettere solo dati Unicode di
esattamente 12 caratteri.
■
Esercizio pratico 4: Utilizzare tipi di dati alias. Creare un tipo di dati alias per fare in
modo che i numeri non abbiano una precisione superiore a due cifre dopo la virgola
decimale.
Eseguire una prova pratica
Le prove pratiche presentate nel CD allegato al manuale offrono diverse opzioni. Ad
esempio, è possibile verificare le proprie conoscenze su un solo obiettivo di esame o
sull’intero contenuto dell’esame di certificazione 70-444. È possibile impostare la prova in
modo da simulare esattamente la situazione reale di un esame di certificazione, oppure
impostare la modalità studio per poter consultare le risposte corrette e le spiegazioni dopo
aver dato la propria risposta.
Maggiori informazioni
Prove pratiche
Per maggiori informazioni sulle opzioni disponibili per le prove pratiche, consultare la sezione
“Come utilizzare le prove pratiche” nell’introduzione del manuale.