DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS

 

Differences between UNIQUE Constraint and Index

By Kalman Toth, M.Phil. Physics, M.Phil. Comp. Science, MCDBA, MCITP

May 1 , 2011

The differences between UNIQUE constraint and index are quite subtle since UNIQUE constraint in SQL Server is implemented as UNIQUE index.

UNIQUE constraint is a database object. UNIQUE index is not a database object. UNIQUE constraint creates a UNIQUE (candidate) KEY which can be used for Foreign Key referencing according to ANSI SQL rules. T-SQL allows FK referencing to UNIQUE index columns also, however, this is not an ANSI feature.

The usage syntax is different. For example, index cannot be created in CREATE TABLE. Differences vis-a-vis table variables as demonstrated following.

-- UNIQUE constraint can be defined on table variable, UI cannot

DECLARE @Omega TABLE (OmegaID int identity(1,1) PRIMARY KEY,

      Name varchar(48) UNIQUE,

      Address varchar(48) );

 

INSERT @Omega(Name, Address) SELECT 'Sonakshi Sinha', '1 Main Street, Mumbai';

-- (1 row(s) affected)

CREATE UNIQUE INDEX idxOmg on @Omega(Address);

/*Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '@Omega'.  */

Related system commands are different:

exec sp_helpindex Alpha

exec sp_helpconstraint Alpha

The following script with results demonstrate similarities and differences.

USE AdventureWorks2008;

GO

CREATE TABLE Alpha (AlphaID int identity(1,1) PRIMARY KEY,

  UQCol varchar(32) UNIQUE,  -- unique constraint

  UICol varchar(32) NULL,    -- unique index

  ModifiedDate datetime default (CURRENT_TIMESTAMP));

GO

CREATE UNIQUE INDEX idxUICol on Alpha(UICol);

GO

 

INSERT Alpha(UQCol,UICol) VALUES ('New York', 'Paris')

-- (1 row(s) affected)

INSERT Alpha(UQCol,UICol) VALUES (NULL, NULL)

-- (1 row(s) affected)

INSERT Alpha(UQCol,UICol) VALUES (NULL, 'Berlin')

/*  Msg 2627, Level 14, State 1, Line 1

Violation of UNIQUE KEY constraint 'UQ__Alpha__A93B77A3403A8C7D'.

Cannot insert duplicate key in object 'dbo.Alpha'.

The duplicate key value is (<NULL>).

The statement has been terminated. */

INSERT Alpha(UQCol,UICol) VALUES ('Montreal', NULL)

/*Msg 2601, Level 14, State 1, Line 1

Cannot insert duplicate key row in object 'dbo.Alpha' with unique index 'idxUICol'.

The duplicate key value is (<NULL>).

The statement has been terminated.

*/

INSERT Alpha(UQCol,UICol) VALUES ('Los Angeles', 'Budapest')

-- (1 row(s) affected)

GO

CREATE TABLE Beta (BetaID int identity(1,1) PRIMARY KEY,

  UQColID varchar(32) FOREIGN KEY REFERENCES Alpha(UQCol),    -- unique constraint FK

  UIColID varchar(32) FOREIGN KEY REFERENCES Alpha(UICol),    -- unique index FK

  ModifiedDate datetime default (CURRENT_TIMESTAMP));

GO

SELECT * FROM Alpha

GO

/*AlphaID   UQCol UICol ModifiedDate

1     New York    Paris 2011-05-07 12:18:02.430

2     NULL  NULL  2011-05-07 12:18:02.467

5     Los Angeles Budapest    2011-05-07 12:18:02.473  */

 

INSERT Beta(UQColID,UIColID) VALUES ('New York', 'Paris')

-- (1 row(s) affected)

INSERT Beta(UQColID,UIColID) VALUES ('New York City', 'Paris')

/*Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Beta__UQColID__17F790F9".

The conflict occurred in database "tempdb", table "dbo.Alpha", column 'UQCol'.

The statement has been terminated. */

INSERT Beta(UQColID,UIColID) VALUES ('New York', 'Paris, France')

/*Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Beta__UIColID__18EBB532".

The conflict occurred in database "tempdb", table "dbo.Alpha", column 'UICol'.

The statement has been terminated. */

GO

 

-- Unique index is not listed as constraint in database metadata

SELECT *

  FROM [AdventureWorks2008].[INFORMATION_SCHEMA].[TABLE_CONSTRAINTS]

  WHERE CONSTRAINT_TYPE ='UNIQUE'

/* CONSTRAINT_CATALOG   CONSTRAINT_SCHEMA CONSTRAINT_NAME   TABLE_CATALOG     TABLE_SCHEMA     

TABLE_NAME  CONSTRAINT_TYPE   IS_DEFERRABLE     INITIALLY_DEFERRED

AdventureWorks2008      dbo   UQ__Alpha__A93B77A35614BF03   AdventureWorks2008      dbo  

Alpha UNIQUE      NO    NO  */

GO

DROP TABLE tempdb.dbo.Beta

DROP TABLE tempdb.dbo.Alpha

GO

------------ 

 

Related article:

SQL SERVER Difference Between Unique Index vs Unique Constraint

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE