This article gives you an overview of Unique Constraints in SQL and also the Unique SQL Server index. Along the way, we will look at the differences between them.
Introduction
Constraints in SQL Server allows defining the rules at the column level in the SQL table. We can add a constraint using the Create table or Alter table statement. SQL Server enforces ACID properties – Atomicity, Consistency, Isolation and Durability for a SQL Server transaction.
We use Constraints for the Consistency property of an ACID. It means that only valid data that satisfies the condition should exist in the database.
You can go through this article, SQL Server Transaction Overview to learn about ACID properties.
In this article, we will explore SQL Server Unique Indexes and Unique constraints. We will also go over the difference between them.
Overview of UNIQUE constraints in SQL Server
We can ensure unique value in a column of SQL Server. It can be either on a single column or a combination of columns. It prevents you from having duplicate values in columns tied with the unique constraint. You might be familiar with a primary key column that also enforces unique value in the column. We can have only one primary key per table in SQL Server.
Suppose you have an employee table and as its name suggests it holds all employee’s information. We have a primary key for the [EmployeeID] column. This table also holds the social security number of employees. We do not want any duplicate value in this social security number column. We do not have the option to define the primary key because our table already has it.
Let’s create a SQL table using the SSMS GUI method. Expand the database and right-click on Tables-> New->Table.
Specify columns, their data type and remove the check for the Allow Nulls column.
Right-click on the [EmployeeID] column and enable the Primary Key by clicking Set Primary key on it.
It puts a key symbol for the primary key column, as shown below.
Now, right-click on the [SocialSecurityNumber] column and choose Indexes/Keys.
It opens the following indexes/keys wizard that shows existing indexes like we already have a primary key on [Employee] table.
Click on Add, and we can define additional index/constraints using this.
In the General group, select the column in which we want to define a SQL Server Index. We can select the data sort order in ascending (default) or descending order.
In this SQL Server index properties, we can select a value for the property- IsUnique.
![[IsUnique] property](/wp-content/uploads/2020/06/isunique-property.png)
In the type, you get an option to choose from the Unique key or Index.
Let’s select the Unique Key, and you see that the previous option “Is Unique” is greyed out. We cannot make any change here because the unique key is for unique value in a column.
SSMS gives you the option to generate the script for the work you did on the GUI. It is a good thing, especially for a beginner to learn both GUI and t-SQL.
Click on the Generate Change Script…, and you get t-SQL for Create table, add primary key constraint and add the unique constraint in SQL Server.
Copy this script and close the table designer window without saving it. We use the generated script to create the table and unique constraint in SQL Server. I modified the table name to have an appropriate name for our 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 [PRIMARY] 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 [PRIMARY] 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 [PRIMARY] GO ALTER TABLE dbo.Employee SET (LOCK_ESCALATION = TABLE) GO COMMIT |
Execute the above script, and it creates the table, primary key and unique key constraint. It creates the SQL Server index for primary key constraints and unique key constraints. We can check existing indexes on a table using sys.sp_helpindex system stored procedure.
|
1 2 |
EXEC sys.sp_helpindex @objname = N'Employee' GO |
Let’s try to insert few values in this table and see if we are allowed to enter duplicates in the [SocialSecurityNumber] column.
In the below query, Ram tries to enter the value in the [SocialSecurityNumber] column that is already available for the employee 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) |
It inserts the first two rows successfully, but for third-row, you get a message about the Unique key constraint violation. You get the duplicate value in the output as well. It helps you to figure out the problematic insert statement causing issues quickly.
Disabling Unique Constraints in SQL Server
We can disable a unique constraint using the following ALTER table statement.
|
1 2 3 |
ALTER TABLE Employee NOCHECK CONSTRAINT ALL GO |
This command executed successfully.
If we try to enter the duplicate value, still we get the same error message.
We know that the unique constraint in SQL Server creates a unique SQL Server index as well. SQL Server allows us to disable an index as well without dropping it.
Right-click on the SQL Server index that we wish to disable and click on Disable as shown below.
Alternatively, we can use the ALTER INDEX command and disable the SQL Server index. You need to specify the index name and table name in this query.
|
1 2 |
ALTER INDEX IX_Unique_SSN ON Employee DISABLE |
It allows you to enter the duplicate value in the [SocialSecurityNumber] column.
We have a duplicate value in the table. Let’s enable the unique non-clustered Index. To enable the Index, we need to rebuild it.
To rebuild an index, either right-click on Index and click on REBUILD from its properties.
We can also rebuild using the following ALTER INDEX command.
|
1 2 3 |
USE [SQLShack] GO ALTER INDEX [IX_Unique_SSN] ON [dbo].[Employee] REBUILD |
We cannot enable the Index because a duplicate key exists, and the unique key constraint does not allow duplicates. It also gives you duplicate keys in the output.
Drop unique constraints in SQL Server
We cannot drop the unique Index created by the unique constraints. If we try to do so, it gives you the following error message. It does not allow an explicit drop index because the unique constraint is using the Index.
|
1 2 |
DROP INDEX Employee.[IX_Unique_SSN] GO |
We can drop the Index using the Alter Table Drop Constraint command. As we know, SQL Server creates an index with a unique constraint in SQL Server. This command drops the Index along with the constraint.
|
1 2 |
ALTER TABLE Employee DROP CONSTRAINT IX_Unique_SSN; |
We can verify in the following screenshot that Index does not exist now.
It provides you with an additional benefit that no one can accidentally delete the unique Index created by the unique constraint.
Unique Index in SQL Server
Previously we created unique constraints in SQL Server using the SSMS GUI method. We do not have any existing unique constraints for the [Employee] table.
Let’s right-click on [Employee] table and select Design. It opens the table designer as we saw earlier.
Right-click on [SocialSecurityNumber], select Index/keys, and choose Index from the Type column. For a unique index, select the value Yes for the [Is Unique] column.
We also have the few options enabled for unique Indexes such as Ignore duplicate keys and Re-compute statistics.
Close this index/keys page and generate the script. You can see it creates a unique non-clustered index for the [Employee] table.
Click Ok and save the modifications you did. You get the error message because it found a duplicate key for the [SocialSecurityNumber] column.
We can remove the duplicate, and it creates the Index for you.
Difference between Unique Indexes and Unique Constraints in SQL Server
Both the unique index and unique constraint are similar, and there is no functional difference between them. Query optimizer also uses the Unique Index to create cost-optimized execution plans. The only difference is that you cannot directly drop the unique Index created by the unique constraint in SQL Server. You also get a few additional index options once you directly create a unique Index.
As we know, constraints are like a business rule for data stored in SQL Server tables. You should create a unique constraint when you do not directly deal with the Index. However, you should not define a unique constraint and key on similar columns. It might slow down your queries, and you have duplicate indexes as well.
We cannot differentiate between a unique key and Index by looking at indexes in GUI. Both exist in the same index folder in a database.
However, SQL Server knows the difference. If we script out both Indexes, you can see different scripts for both indexes. As we can see below, the first script uses an alter table with Add Constraint clause for a unique constraint in SQL Server while the later part uses the Create Non-Clustered Index statement.
Still, you get some additional advantages with unique Index created explicitly over Unique Constraints in SQL Server.
- You can include columns in a non-clustered unique index to improve query performance. SQL Server enforces uniqueness only for the key column of a unique index
- We can add a filter in a unique index. It can be useful if you want to create a unique index on a column that allows NULL values. In this case, we can have multiple NULL values because a unique Index will be created for non-null values
- We can also define a foreign key that reference a unique key index
Conclusion
In this article, we explored unique constraints and unique indexes in SQL Server. We get a unique index as well if you create a unique constraint. You can decide whatever option works for you as there is no difference in query performance.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023