Questo articolo vi dà una panoramica dei vincoli unici in SQL e anche dell’indice unico in SQL Server. Lungo il percorso, vedremo le differenze tra loro.
Introduzione
I vincoli in SQL Server permettono di definire le regole a livello di colonna nella tabella SQL. Possiamo aggiungere un vincolo usando l’istruzione Create table o Alter table. SQL Server applica le proprietà ACID – Atomicità, Consistenza, Isolamento e Durabilità per una transazione SQL Server.
Usiamo i vincoli per la proprietà Consistenza di un ACID. Significa che solo i dati validi che soddisfano la condizione devono esistere nel database.
Puoi leggere questo articolo, Panoramica sulle transazioni di SQL Server per imparare le proprietà ACID.
In questo articolo, esploreremo gli indici unici e i vincoli unici di SQL Server. Esamineremo anche la differenza tra loro.
Panoramica dei vincoli UNIQUE in SQL Server
Possiamo assicurare un valore unico in una colonna di SQL Server. Può essere sia su una singola colonna che su una combinazione di colonne. Impedisce di avere valori duplicati nelle colonne legate con il vincolo unico. Potreste avere familiarità con una colonna chiave primaria che impone anche un valore unico nella colonna. Possiamo avere solo una chiave primaria per tabella in SQL Server.
Supponiamo di avere una tabella dei dipendenti e come suggerisce il nome contiene tutte le informazioni dei dipendenti. Abbiamo una chiave primaria per la colonna. Questa tabella contiene anche il numero di sicurezza sociale dei dipendenti. Non vogliamo nessun valore duplicato in questa colonna del numero di sicurezza sociale. Non abbiamo la possibilità di definire la chiave primaria perché la nostra tabella ce l’ha già.
Creiamo una tabella SQL usando il metodo SSMS GUI. Espandiamo il database e clicchiamo con il tasto destro su Tables-> New->Table.
Specifica le colonne, il loro tipo di dati e rimuovi il controllo per la colonna Allow Nulls.
Cliccate con il tasto destro sulla colonna e abilitate la chiave primaria cliccando su Set Primary key.
Mette un simbolo di chiave per la colonna chiave primaria, come mostrato sotto.
Ora, cliccate con il tasto destro sulla colonna e scegliete Indici/chiavi.
Si apre il seguente wizard indici/chiavi che mostra gli indici esistenti come se avessimo già una chiave primaria sulla tabella.
Clicca su Aggiungi, e possiamo definire ulteriori indici/ vincoli utilizzando questo.
Nel gruppo Generale, selezionare la colonna in cui vogliamo definire un indice SQL Server. Possiamo selezionare l’ordinamento dei dati in ordine ascendente (default) o discendente.
In queste proprietà dell’indice SQL Server, possiamo selezionare un valore per la proprietà IsUnique.
Nel tipo, si ottiene un’opzione per scegliere tra la chiave unica o l’indice.
Selezioniamo la chiave unica, e si vede che l’opzione precedente “Is Unique” è in grigio. Non possiamo fare alcun cambiamento qui perché la chiave unica è per un valore unico in una colonna.
SSMS vi dà la possibilità di generare lo script per il lavoro fatto sulla GUI. È una buona cosa, specialmente per un principiante, imparare sia la GUI che il t-SQL.
Cliccate su Genera script di modifica…, e otterrete t-SQL per creare una tabella, aggiungere il vincolo di chiave primaria e aggiungere il vincolo unico in SQL Server.
Copiamo questo script e chiudiamo la finestra del table designer senza salvarlo. Usiamo lo script generato per creare la tabella e il vincolo unico in SQL Server. Ho modificato il nome della tabella per avere un nome appropriato per la nostra demo.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Employee
(
EmployeeID int NOT NULL,
EmpName varchar(50) NOT NULL,
SocialSecurityNumber int NOT NULL
) ON
GO
ALTER TABLE dbo.Employee ADD CONSTRAINT
PK_Table_1 PRIMARY KEY CLUSTERED
(
EmployeeID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
GO
ALTER TABLE dbo.Employee ADD CONSTRAINT
IX_Unique_SSN UNIQUE NONCLUSTERED
(
SocialSecurityNumber
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
GO
ALTER TABLE dbo.Employee SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
|
Eseguire lo script sopra, e crea la tabella, la chiave primaria e il vincolo di chiave unica. Crea l’indice SQL Server per i vincoli di chiave primaria e i vincoli di chiave unica. Possiamo controllare gli indici esistenti su una tabella usando la stored procedure di sistema sys.sp_helpindex.
|
1
2
|
EXEC sys.sp_helpindex @objname = N’Employee’
GO
|
Proviamo a inserire alcuni valori in questa tabella e vediamo se siamo autorizzati a inserire duplicati nella colonna.
Nella query seguente, Ram cerca di inserire il valore nella colonna che è già disponibile per il dipendente Raj.
|
1
2
3
|
Insert into Employee values(1,’Raj’,1111)
Insert into Employee values(2,’Shyam’,2222)
Insert into Employee values(3,’Ram’,1111)
|
Inserisce le prime due righe con successo, ma per la terza riga, si ottiene un messaggio sulla violazione del vincolo Unique key. Si ottiene anche il valore duplicato nell’output. Ti aiuta a capire quale sia l’istruzione di inserimento problematica che causa problemi velocemente.
Disabilitare i vincoli unici in SQL Server
Possiamo disabilitare un vincolo unico usando la seguente istruzione ALTER table.
|
1
2
3
|
ALTER TABLE Employee
NOCHECK CONSTRAINT ALL
GO
|
Questo comando è stato eseguito correttamente.
Se proviamo a inserire il valore duplicato, otteniamo ancora lo stesso messaggio di errore.
Sappiamo che il vincolo unico in SQL Server crea anche un indice unico in SQL Server. SQL Server ci permette anche di disabilitare un indice senza eliminarlo.
Clicca con il tasto destro del mouse sull’indice SQL Server che vogliamo disabilitare e clicca su Disabilita come mostrato qui sotto.
In alternativa, possiamo usare il comando ALTER INDEX e disabilitare l’indice di SQL Server. È necessario specificare il nome dell’indice e il nome della tabella in questa query.
|
1
2
|
ALTER INDEX IX_Unique_SSN ON Employee
DISABLE
|
Vi permette di inserire il valore duplicato nella colonna.
Abbiamo un valore duplicato nella tabella. Abilitiamo l’indice unico non clusterizzato. Per abilitare l’indice, dobbiamo ricostruirlo.
Per ricostruire un indice, clicchiamo con il tasto destro su Index e clicchiamo su REBUILD dalle sue proprietà.
Possiamo anche ricostruire usando il seguente comando ALTER INDEX.
|
1
2
3
|
USE
GO
ALTER INDEX ON . REBUILD
|
Non possiamo abilitare l’indice perché esiste una chiave duplicata e il vincolo di chiave unica non permette duplicati. Dà anche delle chiavi duplicate nell’output.
Rimuovere i vincoli unici in SQL Server
Non possiamo rimuovere l’indice unico creato dai vincoli unici. Se proviamo a farlo, ci dà il seguente messaggio di errore. Non permette un drop index esplicito perché il vincolo unico sta usando l’indice.
|
1
2
|
DROP INDEX Employee.
GO
|
Possiamo eliminare l’indice usando il comando Alter Table Drop Constraint. Come sappiamo, SQL Server crea un indice con un vincolo unico in SQL Server. Questo comando elimina l’indice insieme al vincolo.
|
1
2
|
ALTER TABLE Employee
DROP CONSTRAINT IX_Unique_SSN;
|
Possiamo verificare nel seguente screenshot che l’indice non esiste ora.
Vi fornisce un ulteriore vantaggio che nessuno può cancellare accidentalmente l’indice unico creato dal vincolo unico.
Indice unico in SQL Server
In precedenza abbiamo creato vincoli unici in SQL Server utilizzando il metodo SSMS GUI. Non abbiamo nessun vincolo unico esistente per la tabella.
Clicchiamo con il tasto destro sulla tabella e selezioniamo Design. Si apre il table designer come abbiamo visto prima.
Clicchiamo con il tasto destro su , selezioniamo Index/keys, e scegliamo Index dalla colonna Type. Per un indice unico, selezionate il valore Sì per la colonna.
Abbiamo anche le poche opzioni abilitate per gli indici unici come Ignore duplicate keys e Re-compute statistics.
Chiudi questa pagina indice/chiavi e genera lo script. Potete vedere che crea un indice unico non-cluster per la tabella.
Clicca Ok e salva le modifiche che hai fatto. Ottieni il messaggio di errore perché ha trovato una chiave duplicata per la colonna.
Possiamo rimuovere il duplicato, e crea l’indice per te.
Differenza tra indici unici e vincoli unici in SQL Server
Sia l’indice unico che il vincolo unico sono simili, e non ci sono differenze funzionali tra loro. L’ottimizzatore di query utilizza anche l’indice unico per creare piani di esecuzione ottimizzati per i costi. L’unica differenza è che non è possibile eliminare direttamente l’indice unico creato dal vincolo unico in SQL Server. Si ottengono anche alcune opzioni di indice aggiuntive una volta creato direttamente un indice unico.
Come sappiamo, i vincoli sono come una regola di business per i dati memorizzati nelle tabelle di SQL Server. Si dovrebbe creare un vincolo unico quando non si ha a che fare direttamente con l’indice. Tuttavia, non dovreste definire un vincolo unico e una chiave su colonne simili. Potrebbe rallentare le vostre query, e avete anche degli indici duplicati.
Non possiamo distinguere tra una chiave unica e un indice guardando gli indici nella GUI. Entrambi esistono nella stessa cartella indice in un database.
Tuttavia, SQL Server conosce la differenza. Se facciamo lo script di entrambi gli indici, si possono vedere script diversi per entrambi gli indici. Come possiamo vedere qui sotto, il primo script usa una tabella alter con la clausola Add Constraint per un vincolo unico in SQL Server mentre la parte successiva usa l’istruzione Create Non-Clustered Index.
Ancora, si ottengono alcuni vantaggi aggiuntivi con un indice unico creato esplicitamente rispetto ai vincoli unici in SQL Server.
- È possibile includere colonne in un indice unico non-cluster per migliorare le prestazioni della query. SQL Server applica l’unicità solo per la colonna chiave di un indice unico
- Possiamo aggiungere un filtro in un indice unico. Può essere utile se si vuole creare un indice unico su una colonna che permette valori NULL. In questo caso, possiamo avere più valori NULL perché un indice unico sarà creato per i valori non nulli
- Possiamo anche definire una chiave esterna che fa riferimento a un indice unico
Conclusione
In questo articolo, abbiamo esplorato i vincoli unici e gli indici unici in SQL Server. Otteniamo anche un indice unico se si crea un vincolo unico. Potete decidere qualsiasi opzione che funzioni per voi, dato che non c’è differenza nelle prestazioni della query.
- Autore
- Post recenti
È il creatore di una delle più grandi raccolte gratuite online di articoli su un singolo argomento, con la sua serie di 50 parti su SQL Server Always On Availability Groups. Sulla base del suo contributo alla comunità di SQL Server, è stato riconosciuto con vari premi tra cui il prestigioso “Miglior autore dell’anno” continuamente nel 2020 e 2021 a SQLShack.
Raj è sempre interessato a nuove sfide quindi se avete bisogno di aiuto di consulenza su qualsiasi argomento trattato nei suoi scritti, può essere raggiunto all’indirizzo [email protected]
Vedi tutti i post di Rajendra Gupta
- Configurare la conservazione del backup a lungo termine per il database Azure SQL – 25 marzo, 2021
- Creare una copia transazionalmente coerente del database Azure SQL – 22 marzo 2021
- Una panoramica di Azure Cloud Shell – 18 marzo 2021