en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP

 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize

Recent Entries

Minimize
אוג16

Written by: ronen ariely
16/08/2015 21:45 RssIcon


Introduction

The question "what is the difference between Unique Constraints and Unique Indexes" always rises in forums. Instead of writing this every time in the each forum and each question, I have decided to post this blog. There are several articles that you can find online regarding this issue. This blog does not intend to replace a full tutorial, but it is more like a preview or a summary of the issue, by showing several differences in practice.

Our Case study

I will go step by step from creating a simple table, adding Unique Constraint and Unique Indexes, while comparing several differences.

create table T1 (id int, txt nvarchar(100))
GO
 
exec sp_helpindex T1
GO
-- The object 'T1' does not have any indexes, or you do not have permissions.

let's add Unique Constraint and and execute sp_helpindex again:

ALTER TABLE T1
  ADD CONSTRAINT uqc_T1_id
  UNIQUE (id)
GO
 
exec sp_helpindex T1
GO
/*
uqc_T1_id   nonclustered, unique, unique key located on PRIMARY id
*/

Conclusion: UNIQUE CONSTRAINT is applied by creating nonclustered unique index.

Let's create a nonclustered unique index, and check if these are actually the same:

CREATE UNIQUE NONCLUSTERED INDEX uix_T1_id ON T1(id)
GO
 
exec sp_helpindex T1
GO
 
/*
uix_T1_txt    nonclustered, unique located on PRIMARY                  id
uqc_T1_id     nonclustered, unique, unique key located on PRIMARY      id
*/

Conclusionconstraint has the additional clause "unique key".

FOREIGN KEY behavior

This is not a primary key, but yet it is a unique key. We probably expect to have no issues, while creating another table with FOREIGN KEY to our Unique Constraint (which is unique key).  let's examine creating FOREIGN KEY to our Unique Constraint and to our Unique Index. Do not execute this yet!

create table A (id int, txt nvarchar(100))
GO
 
ALTER TABLE A
  ADD CONSTRAINT uqc_A_id
  UNIQUE (id)
GO
 
create table B (id int, A_ID int FOREIGN KEY REFERENCES A(id))
GO

can you notice a problem?

The SSMS interface shouts that there is an error in the code. Notice the red line under the table name A in the REFERENCES.

Creating FOREIGN KEY, which REFERENCES a UNIQUE CONSTRAINT work OK, in spite the fact that the SSMS shouts for error: There are no primary or candidate keys in the referenced table 'T1' that match the referencing column list in the foreign key... You can execute the code and examine the issue. This is a bug in the SSMS. We can create the FOREIGN KEY after the table was created, as well. In this case there is no alert regarding primary or candidate key.

ALTER TABLE B
    ADD CONSTRAINT fk_T1_ID FOREIGN KEY (A_ID)
REFERENCES A(id)

* you can repeat the same steps with Unique Index instead of Unique Constraint and it will work OK as well. In this case there will be no alert for error in the SSMS.

create table A (id int, txt nvarchar(100))
GO
CREATE UNIQUE NONCLUSTERED INDEX uix_A_id ON A(id)
GO
create table B (id int, A_ID int FOREIGN KEY REFERENCES A(id))
GO

disable Uniqueness

Can we Disable the constraint like we disable indexes?

Well, I did not find any option in the BOL except using NOCHECK option while altering the table, but this work only on foreign key and check constraints and will not help on primary key. Let's check the object ObjectProperty and before and after we try to disable it.

SELECT ObjectProperty(object_id('uix_T1_id'),'CnstIsDisabled') -- NULL
SELECT ObjectProperty(object_id('uqc_T1_id'),'CnstIsDisabled') -- 0: indicating is not disabled.
GO
 
ALTER TABLE T1 NOCHECK CONSTRAINT uqc_T1_id
GO

on old servers version 2000 the action do not raise an error but the Object Property stay the same, while on newer version (I confirmed on 2014/2016) it raise an error: Object 'uqc_T1_id' cannot be disabled or enabled. This action applies only to foreign key and check constraints.

ConclusionPRIMARY Constraint can not be disable while index can

So, we saw that the constraint act like an index while executing the SP sp_helpindex... will the index will act as constraint?

-->> Sets locking option
exec sp_indexoption @IndexNamePattern  = 'T1.uix_T1_id', @OptionName = 'AllowRowLocks', @OptionValue = true
exec sp_indexoption @IndexNamePattern  = 'T1.uqc_T1_id', @OptionName = 'AllowRowLocks', @OptionValue = true
GO -- work for both
 
-->> get all constraints
exec sp_helpconstraint T1
GO

Conclusionwhile we can configure some option for both like the locking option, with a CREATE INDEX, we can specify options that we can not configure for the constraint like: PAD_INDEX, FILLFACTOR, IGNORE_DUP_KEY, DROP_EXISTING, STATISTICS_NORECOMPUTE, SORT_IN_TEMPDB.

Summary

It is obvious from the above examples, that there is lot of similarities between Unique Constraints and Unique Indexes, but they are not equivalent! Therefore, we should use the right option, for each case.