Rajendra Gupta
SQL ACID properties

Difference between Unique Indexes and Unique Constraints in SQL Server

June 10, 2020 by

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.

SQL 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.

Create a new table

Specify columns, their data type and remove the check for the Allow Nulls column.

Table designer

Right-click on the [EmployeeID] column and enable the Primary Key by clicking Set Primary key on it.

Set Primary Key

It puts a key symbol for the primary key column, as shown below.

Primary key symbol

Now, right-click on the [SocialSecurityNumber] column and choose Indexes/Keys.

Constraints in SQL: Indexes/keys

It opens the following indexes/keys wizard that shows existing indexes like we already have a primary key on [Employee] table.

Create a new SQL Server Index

Click on Add, and we can define additional index/constraints using this.

Primary Unique index

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.

Data sort order

In this SQL Server index properties, we can select a value for the property- IsUnique.

[IsUnique] property

In the type, you get an option to choose from the Unique key or Index.

Choose Index or Unique key

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.

Select Unique key

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.

Generate change script

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.

Constraints in SQL: Add primary key constraint

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.

Script Overview

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.

System stored procedure

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.

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.

Duplicate key

Disabling Unique Constraints in SQL Server

We can disable a unique constraint using the following ALTER table statement.

This command executed successfully.

Unique Constraint in SQL Server

If we try to enter the duplicate value, still we get the same error message.

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.

Constraints in SQL: Disable index

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.

It allows you to enter the duplicate value in the [SocialSecurityNumber] column.

Duplicate values

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.

REBUILD index

We can also rebuild using the following ALTER INDEX command.

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.

Duplicate key error

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.

Drop a Unique constraint

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.

We can verify in the following screenshot that Index does not exist now.

Verify index

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.

Unique Index in SQL Server

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.

Index type

We also have the few options enabled for unique Indexes such as Ignore duplicate keys and Re-compute statistics.

Ignore duplicate keys

Close this index/keys page and generate the script. You can see it creates a unique non-clustered index for the [Employee] table.

Constraints in SQL: View script

Click Ok and save the modifications you did. You get the error message because it found a duplicate key for the [SocialSecurityNumber] column.

Save the modifications

We can remove the duplicate, and it creates the Index for you.

View index and index keys

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.

Index difference

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.

Unique key

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.

Constraints in SQL: Non-Clustered Index

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.

Rajendra Gupta
Indexes

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is 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 his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

18,918 Views