Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Difference between SQL Server Unique Indexes and Unique Constraints


By:   |   Last Updated: 2016-06-13   |   Comments (9)   |   Related Tips: More > Indexing

Problem

We can enforce uniqueness of values in specific column(s) in a SQL Server table by creating a unique constraint or a unique index on those column(s).  What's the difference and how does SQL Server handle these differently?

Solution

Before getting started, let me briefly describe SQL Server unique indexes vs. unique constraints:

  • A unique index ensures that the values in the index key columns are unique.
  • A unique constraint also guarantees that no duplicate values can be inserted into the column(s) on which the constraint is created. When a unique constraint is created a corresponding unique index is automatically created on the column(s).

Let's investigate these concepts with an example.  First, let's create a test environment:

USE master 
GO

-- Creating database
CREATE DATABASE TestDB
GO

USE TestDB
GO

-- Creating table
CREATE TABLE TestTable
(
 ID INT,
 Value INT,
 NewValue INT,
 CONSTRAINT PK_TestTable_ID PRIMARY KEY (ID)
)

GO

Creating a SQL Server Unique Index or Unique Constraint

Suppose we need to enforce the uniqueness of the data in the "Value" field in the TestTable. We can accomplish this with either SQL Server Management Studio (SSMS) or T-SQL code.

In SSMS we can right click on the table name, then choose "Design", then right-click on the table design window and choose "Indexes/Keys" and add a new index on the "Value" column by choosing "Yes" in the "Is Unique" field as shown below:

Creating a SQL Server Unique Index or Unique Constraint in SQL Server Management Studio

In the screen shot above, we can see that for enforcing uniqueness we can choose either "Index" or "Unique Key" in the "Type" field. In both cases SQL Server by default creates a unique non-clustered index (if there is no clustered index on the table we can create a unique index/key as clustered) on the selected column(s).  In this situation, the type of index does not impact the query plan selected by the SQL Server engine and there were no performance differences based on my testing.

However we can see that when we choose "Unique Key" some index creation options become unavailable (Ignore Duplicate Keys and Re-compute statistics):

Creating a SQL Server Unique Key in SQL Server Management Studio

If we choose "Index", theses options are available:

Creating a SQL Server Unique Index in SQL Server Management Studio

Let's do the same thing using T-SQL code (if you have saved the previous changes in SSMS, you need to drop the created index, so it can be created using T-SQL):

USE TestDB
GO

-- Creating unique index
CREATE UNIQUE INDEX UIX_TestTable_Value ON TestTable(Value)
GO

--Creating unique constraint
ALTER TABLE TestTable
ADD CONSTRAINT UC_TestTable_NewValue
UNIQUE (NewValue)
GO

As a result we can see that there are two unique non-clustered indexes on the table TestTable:

two unique non-clustered indexes on the table TestTable in SSMS

We can also see these indexes using T-SQL:

USE TestDB
GO

EXEC sys.sp_helpindex @objname = N'TestTable' 
GO

two unique non-clustered indexes on the table TestTable via T-SQL

When creating a unique constraint with T-SQL, some index creation options are unavailable just like in SSMS.  Here we will test this to see what happens.

USE TestDB
GO

-- Creating unique index
CREATE UNIQUE INDEX UIX_TestTable_Value ON TestTable(Value) WITH IGNORE_DUP_KEY
GO

-- Creating unique constraint
ALTER TABLE TestTable
ADD CONSTRAINT UC_TestTable_NewValue
UNIQUE (NewValue)
WITH IGNORE_DUP_KEY
GO

The first index creation statement succeeds and the index is created, but the second fails:

Unique index creation and constraint creation

Dropping a SQL Server Unique Index or Unique Constraint

Now let's try to drop these indexes:

USE TestDB
GO

-- Dropping indexes
DROP INDEX TestTable.UIX_TestTable_Value
GO

DROP INDEX TestTable.UC_TestTable_NewValue
GO

We can see that the first index has been deleted, however the second cannot be deleted with the "DROP INDEX" command and an error arises:

Unable to issue DROP INDEX for Unique Key Constraint

We need to first drop the unique key constraint:

USE TestDB
GO

--Dropping constraint
ALTER TABLE TestTable DROP CONSTRAINT UC_TestTable_NewValue
GO

Which automatically drops the corresponding index:

Constraint is dropped as shown in SSMS

Disable a SQL Server Unique Constraint

Now let's check if it is possible to disable a unique constraint. The following command disables all constraints on the "TestTable" table:

USE TestDB
GO

-- Creating unique constraint
ALTER TABLE TestTable
ADD CONSTRAINT UC_TestTable_NewValue
UNIQUE (NewValue)
GO

-- Disabling all constraints
ALTER TABLE TestTable
NOCHECK CONSTRAINT ALL 
GO

We will try to insert a duplicate data into "NewValue" column:

USE TestDB
GO

-- Inserting duplicate data
INSERT INTO TestTable(ID, Value, NewValue)
VALUES(1, 1, 2)
GO

INSERT INTO TestTable(ID, Value, NewValue)
VALUES(2, 2, 2)
GO

SELECT * FROM TestTable
GO

The result shows that a "duplicate key violation" occurs:

Duplicate key violation

Only the first row is inserted:

Only the first row is inserted due to the constraint

This means that disabling all constraints on the table does not refer to unique constraints. In other words it is not possible to insert duplicate data into the table by disabling a unique constraint.

Conclusion

Generally there is no functional difference between a unique index and a unique constraint. The latter is also listed as a constraint, however this is only a method to emphasize the purpose of the index. There is no difference for the query optimizer whether the index is created as a unique index or a unique constraint, therefore there is no performance difference. However there are some differences for creation where some index creation options are not available for unique constraints.

Next Steps

Read additional information:



Last Updated: 2016-06-13


get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Sunday, November 11, 2018 - 11:12:59 PM - Enkhbold Back To Top

 

I have some problem with the unique index where i need to actually insert some rows into a table or delete previous values. 

i've deleted the previous data on the table(point_measurement) and my table is now empty, then tried to run my procedure to insert data into the table

and i've got 

Cannot insert duplicate key row in object 'db.point_measurement' with unique index 'U_IX_point_date'. The duplicate key value is (5168, Oct 18 2018  6:00PM, 0).

The statement has been terminated.

I think its what you wrote on 

Disable a SQL Server Unique Constraint

section and i kinda need to be able to insert data into it. I think if i can find the unique indexed data that i'm trying to override or trying to put in a duplicate of, and delete it. i can maybe fix it.

 

What i'm asking is, Is it possible to delete that duplicate key row that is seemingly on the table with some values but not seen on my table, so i can start putting data into it (my system is automated and puts data on to the table daily) as i did before ?

i don't want to mess up my other point's data 


Thursday, October 04, 2018 - 11:05:37 AM - baleng Back To Top

nice article!

it should be

ALTER TABLE TestTable

ADD CONSTRAINT UC_TestTable_NewValue

UNIQUE (NewValue)

WITH (IGNORE_DUP_KEY= ON)

Commands completed successfully.

 

 


Wednesday, February 15, 2017 - 6:04:58 AM - Shyam Kumar Back To Top

Unique Constraint Allows Ignore_Dup_key For Below Script:

ALTER TABLE TestTable
ADD CONSTRAINT PK_TestTable_NewValue
UNIQUE (ID)
WITH (IGNORE_DUP_KEY=ON)

Can Anyone please Explain the Above Scenario

Tuesday, July 05, 2016 - 9:49:30 AM - Edward Pollack Back To Top

Another BIG difference between unique constraints and unique indexes is their inclusion in system view metadata.  For example, SELECT * FROM sys.key_constraints will return data for unique constraints, but NOT unqiue indexes.  As a result, I advocate ALWAYS using unique constraints as they provide better underlying docuemntation & consistency across tables/databases.

An environment mixed with both will eventually result in bad metadata reporting/searching.

An additional important aside: Unique constraints are an ANSI standard, whereas unique indexes are not.

 

 

 

 

 

 

 

 

 


 

 


Tuesday, June 14, 2016 - 10:34:25 AM - Ray Herring Back To Top

 Hmm,

I guess I sit corrected ;)

 

 


Tuesday, June 14, 2016 - 8:06:10 AM - Sergey Gigoyan Back To Top

Ray,

It is possible to create foreign key to reference a unique index. You can test it using this code:

 
USE TestDB
GO
 
CREATE TABLE TestTableA
(
ID INT,
Value INT
)
 
 
CREATE UNIQUE INDEX UIX_TestTableA ON TestTableA(ID) 
 
INSERT INTO TestTableA (ID, Value)
VALUES (1,1),(2,2),(NULL,3),(4,4)
 
CREATE TABLE TestTableB 
(
ID INT, 
TestTableAID INT
)
 
ALTER TABLE TestTableB  WITH CHECK ADD  CONSTRAINT FK_TestTableA_ID FOREIGN KEY(TestTableAID)
REFERENCES TestTableA  (ID)
 
 
INSERT INTO TestTableB (ID, TestTableAID)
VALUES (1,1),(2,4)

Tuesday, June 14, 2016 - 2:36:42 AM - Thomas Franz Back To Top

@Ray: this is not true, the following statements works perfect

[code]

CREATE TABLE t1 (id INT IDENTITY, val1 INT)
CREATE TABLE t2 (id INT IDENTITY, val1 INT)

INSERT INTO t1 (val1) VALUES (1); INSERT INTO t2 (val1) VALUES (1)
INSERT INTO t1 (val1) VALUES (2); INSERT INTO t2 (val1) VALUES (2)

CREATE UNIQUE NONCLUSTERED INDEX idx_t1 ON dbo.t1 (val1)

ALTER TABLE t2 ADD CONSTRAINT fk_t2__t1 FOREIGN KEY (val1) REFERENCES dbo.t1 (val1)
[/code]

 

 

*** NOTE *** - If you want to include code from SQL Server [email protected] Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.


Monday, June 13, 2016 - 6:16:37 PM - Ray Herring Back To Top

 There is a particularly important functional difference between Unique Constraints and Unique Indexes.  A Foreign Key can be defined to reference a Primary Key or a Unique Constraint (essentially a candidate key) but a Foreign Key can not be defined to reference a Unique Index.

See https://msdn.microsoft.com/en-us/library/ms189049.aspx?f=255&MSPPError=-2147217396#Restrictions

 


Monday, June 13, 2016 - 4:01:16 AM - Thomas Franz Back To Top

 Another point: UNIQUE CONSTRAINTs could not be filtered (e.g. you want the column only to be unique, when it is NOT NULL)


Learn more about SQL Server tools