Cet article vous donne un aperçu des contraintes uniques dans SQL et aussi de l’index unique de SQL Server. En cours de route, nous examinerons les différences entre eux.
Introduction
Les contraintes dans SQL Server permettent de définir les règles au niveau de la colonne dans la table SQL. Nous pouvons ajouter une contrainte à l’aide de l’instruction Create table ou Alter table. SQL Server applique les propriétés ACID – Atomicité, Cohérence, Isolation et Durabilité pour une transaction SQL Server.
Nous utilisons les contraintes pour la propriété de cohérence d’une ACID. Cela signifie que seules les données valides qui satisfont à la condition doivent exister dans la base de données.
Vous pouvez parcourir cet article, Présentation des transactions SQL Server, pour connaître les propriétés ACID.
Dans cet article, nous allons explorer les index uniques et les contraintes uniques de SQL Server. Nous passerons également en revue la différence entre elles.
Vue d’ensemble des contraintes UNIQUES dans SQL Server
Nous pouvons assurer une valeur unique dans une colonne de SQL Server. Cela peut être soit sur une seule colonne, soit sur une combinaison de colonnes. Elle vous empêche d’avoir des valeurs dupliquées dans les colonnes liées à la contrainte unique. Vous connaissez peut-être une colonne de clé primaire qui impose également une valeur unique dans la colonne. Nous ne pouvons avoir qu’une seule clé primaire par table dans SQL Server.
Supposons que vous avez une table d’employés et comme son nom l’indique, elle contient toutes les informations des employés. Nous avons une clé primaire pour la colonne. Cette table détient également le numéro de sécurité sociale des employés. Nous ne voulons pas de valeur dupliquée dans cette colonne de numéro de sécurité sociale. Nous n’avons pas la possibilité de définir la clé primaire car notre table en dispose déjà.
Créons une table SQL en utilisant la méthode de l’interface graphique SSMS. Développez la base de données et cliquez avec le bouton droit de la souris sur Tables-> Nouvelle->Table.
Spécifier les colonnes, leur type de données et supprimer la vérification de la colonne Allow Nulls.
Cliquez avec le bouton droit de la souris sur la colonne et activez la clé primaire en cliquant sur Set Primary key on it.
Il met un symbole de clé pour la colonne de clé primaire, comme indiqué ci-dessous.
Maintenant, cliquez avec le bouton droit de la souris sur la colonne et choisissez Indexes/Keys.
Il ouvre l’assistant index/clés suivant qui montre les index existants comme nous avons déjà une clé primaire sur la table.
Cliquez sur Ajouter, et nous pouvons définir des index/contraintes supplémentaires en utilisant ceci.
Dans le groupe Général, sélectionnez la colonne dans laquelle nous voulons définir un index SQL Server. Nous pouvons sélectionner l’ordre de tri des données en ordre ascendant (par défaut) ou descendant.
Dans ces propriétés d’index SQL Server, nous pouvons sélectionner une valeur pour la propriété- IsUnique.
Dans le type, vous avez une option pour choisir entre la clé unique ou l’index.
Sélectionnons la Clé unique, et vous voyez que l’option précédente « Est unique » est grisée. Nous ne pouvons pas faire de changement ici car la clé unique est pour une valeur unique dans une colonne.
SMS vous donne la possibilité de générer le script pour le travail que vous avez fait sur l’interface graphique. C’est une bonne chose, surtout pour un débutant qui doit apprendre à la fois l’interface graphique et le t-SQL.
Cliquez sur le bouton Générer un script de changement…, et vous obtenez le t-SQL pour Créer une table, ajouter une contrainte de clé primaire et ajouter la contrainte unique dans SQL Server.
Copiez ce script et fermez la fenêtre du concepteur de tables sans l’enregistrer. Nous utilisons le script généré pour créer la table et la contrainte unique dans SQL Server. J’ai modifié le nom de la table pour avoir un nom approprié pour notre démo.
|
..
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
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 NON CLASSIFIÉ
(
Numéro de sécurité sociale
) 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
|
Exécutez le script ci-dessus, et il crée la table, la clé primaire et la contrainte de clé unique. Il crée l’index du serveur SQL pour les contraintes de clé primaire et de clé unique. Nous pouvons vérifier les index existants sur une table en utilisant la procédure stockée système sys.sp_helpindex.
|
1
. 2
|
.
EXEC sys.sp_helpindex @objname = N’Employee’
GO
|
Essayons d’insérer quelques valeurs dans cette table et voyons si nous sommes autorisés à saisir des doublons dans la colonne.
Dans la requête ci-dessous, Ram essaie d’entrer la valeur dans la colonne qui est déjà disponible pour l’employé Raj.
|
1
2
3
|
Insérer dans Employee values(1,’Raj’,1111)
Insérer dans Employee values(2,’Shyam’,2222)
Insérer dans Employee values(3, ‘Ram’,1111)
|
Il insère les deux premières lignes avec succès, mais pour la troisième ligne, vous obtenez un message concernant la violation de la contrainte de clé unique. Vous obtenez également la valeur dupliquée dans la sortie. Cela vous aide à déterminer rapidement l’instruction d’insertion problématique à l’origine des problèmes.
Désactivation des contraintes uniques dans SQL Server
Nous pouvons désactiver une contrainte unique en utilisant l’instruction ALTER table suivante.
|
1
2
3
|
ALTER TABLE Employee
NOCHECK CONSTRAINT ALL
GO
|
Cette commande s’est exécutée avec succès.
Si nous essayons de saisir la valeur en double, nous obtenons toujours le même message d’erreur.
Nous savons que la contrainte unique dans SQL Server crée également un index SQL Server unique. SQL Server nous permet de désactiver un index également sans le faire tomber.
Cliquez avec le bouton droit de la souris sur l’index SQL Server que nous souhaitons désactiver et cliquez sur Disable comme indiqué ci-dessous.
Au contraire, nous pouvons utiliser la commande ALTER INDEX et désactiver l’index du serveur SQL. Vous devez spécifier le nom de l’index et le nom de la table dans cette requête.
|
1
2
|
ALTER INDEX IX_Unique_SSN ON Employee
DISABLE
|
Il permet de saisir la valeur en double dans la colonne.
Nous avons une valeur dupliquée dans la table. Activons l’index unique non groupé. Pour activer l’Index, nous devons le reconstruire.
Pour reconstruire un index, il faut soit faire un clic droit sur Index et cliquer sur REBUILD depuis ses propriétés.
Nous pouvons également reconstruire en utilisant la commande ALTER INDEX suivante .
|
1
2
3
|
USE
GO
Alter INDEX ON . REBUILD
|
Nous ne pouvons pas activer l’index car une clé dupliquée existe, et la contrainte de clé unique n’autorise pas les doublons. Cela vous donne également des clés dupliquées dans la sortie.
Drop unique constraints in SQL Server
Nous ne pouvons pas abandonner l’Index unique créé par les contraintes uniques. Si nous essayons de le faire, il vous donne le message d’erreur suivant. Il ne permet pas un drop index explicite car la contrainte unique utilise l’Index.
|
1
2
|
DROP INDEX Employee.
GO
|
Nous pouvons déposer l’index en utilisant la commande Alter Table Drop Constraint. Comme nous le savons, SQL Server crée un index avec une contrainte unique dans SQL Server. Cette commande fait tomber l’index en même temps que la contrainte.
|
1
2
|
ALTER TABLE Employee
DROP CONSTRAINT IX_Unique_SSN ;
|
Nous pouvons vérifier dans la capture d’écran suivante que l’Index n’existe pas maintenant.
Il vous offre un avantage supplémentaire : personne ne peut supprimer accidentellement l’Index unique créé par la contrainte unique.
Index unique dans SQL Server
Vous avez précédemment créé des contraintes uniques dans SQL Server en utilisant la méthode de l’interface graphique SSMS. Nous n’avons pas de contraintes uniques existantes pour la table.
Cliquons à droite sur la table et sélectionnons Design. Cela ouvre le concepteur de table comme nous l’avons vu précédemment.
Cliquez avec le bouton droit sur , sélectionnez Index/clés et choisissez Index dans la colonne Type. Pour un index unique, sélectionnez la valeur Oui pour la colonne.
Nous avons également les quelques options activées pour les index uniques comme Ignorer les clés dupliquées et Recalculer les statistiques.
Fermer cette page d’index/clés et générer le script. Vous pouvez voir qu’il crée un index unique non clusterisé pour la table.
Cliquez sur Ok et enregistrez les modifications que vous avez faites. Vous obtenez le message d’erreur car il a trouvé une clé dupliquée pour la colonne.
On peut supprimer le duplicata, et il crée l’index pour vous.
Différence entre les index uniques et les contraintes uniques dans SQL Server
L’index unique et la contrainte unique sont tous deux similaires, et il n’y a pas de différence fonctionnelle entre eux. L’optimiseur de requêtes utilise également l’index unique pour créer des plans d’exécution optimisés en termes de coûts. La seule différence est que vous ne pouvez pas directement déposer l’index unique créé par la contrainte unique dans SQL Server. Vous obtenez également quelques options d’index supplémentaires une fois que vous créez directement un Index unique.
Comme nous le savons, les contraintes sont comme une règle de gestion pour les données stockées dans les tables SQL Server. Vous devriez créer une contrainte unique lorsque vous ne traitez pas directement avec l’Index. Cependant, vous ne devriez pas définir une contrainte unique et une clé sur des colonnes similaires. Cela pourrait ralentir vos requêtes, et vous avez également des index en double.
On ne peut pas faire la différence entre une clé unique et un index en regardant les index dans l’interface graphique. Les deux existent dans le même dossier d’index dans une base de données.
Par contre, SQL Server connaît la différence. Si nous faisons un script pour les deux index, vous pouvez voir des scripts différents pour les deux index. Comme nous pouvons le voir ci-dessous, le premier script utilise une table alter avec la clause Add Constraint pour une contrainte unique dans SQL Server tandis que la partie suivante utilise l’instruction Create Non-Clustered Index.
Pour autant, vous bénéficiez de quelques avantages supplémentaires avec l’index unique créé explicitement par rapport aux contraintes uniques dans SQL Server.
- Vous pouvez inclure des colonnes dans un index unique non clusterisé pour améliorer les performances des requêtes. SQL Server applique l’unicité uniquement pour la colonne clé d’un index unique
- On peut ajouter un filtre dans un index unique. Cela peut être utile si vous voulez créer un index unique sur une colonne qui autorise les valeurs NULL. Dans ce cas, nous pouvons avoir plusieurs valeurs NULL car un index unique sera créé pour les valeurs non nulles
- Nous pouvons également définir une clé étrangère qui référence un index de clé unique
Conclusion
Dans cet article, nous avons exploré les contraintes uniques et les index uniques dans SQL Server. Nous obtenons également un index unique si vous créez une contrainte unique. Vous pouvez décider de l’option qui vous convient, car il n’y a pas de différence dans les performances des requêtes.
- Auteur
- Postages récents
Rajendra GuptaIl est le créateur de l’une des plus grandes collections gratuites d’articles en ligne sur un seul sujet, avec sa série de 50 articles sur les groupes de disponibilité Always On de SQL Server. Sur la base de sa contribution à la communauté SQL Server, il a été récompensé par divers prix, notamment le prestigieux « Meilleur auteur de l’année » de manière continue en 2020 et 2021 à SQLShack.
Raj est toujours intéressé par de nouveaux défis, donc si vous avez besoin d’aide de consultation sur n’importe quel sujet couvert dans ses écrits, il peut être joint à [email protected]
Voir tous les messages de Rajendra Gupta
- Configurer la rétention de sauvegarde à long terme pour la base de données Azure SQL – 25 mars, 2021
- Créer une copie cohérente sur le plan transactionnel de la base de données Azure SQL – 22 mars 2021
- Un aperçu d’Azure Cloud Shell – 18 mars 2021
.