datetime century date into pad dynamic cursor money percent sp job isnumeric isdate over update
SQLUSA.com
SQL 2012 PROGRAMMING
FREE TRIAL  SQL 2012 PROBRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS

 

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

 

SQL 2008 GRAND SLAM
SQLUSA.com Home Page
SQL Server Training at http://www.sqlusa.com/.
SQL Server 2012 Training Videos at http://www.sqlusa.com/.
SQL Server 2008 Video Training at http://www.sqlusa.com/.
SQL Server 2005 Training Videos at http://www.sqlusa.com/.
Accounting
Administrative
Advertising
Arts
Architecture
Banking
Business Intelligence
Career Jobs
Celebrity
Computer
Consulting
Customer Service
Education
Engineering
Entertainment
Entry Level
Executive
Federal
Finance
Government
Hardware
Healthcare
Hospital
Human Resources
Information Technology
Insurance
Internet
Job Openings
Laboratory
Law Enforcement
Legal
Logistics
Manufacturing
Marketing
Medical
Military
Nursing
Pharmaceutical
Physician
Public Relations
Publishing
Real Estate
Restaurant
Retail
Sales
Social Media
Software
SQL Database
Telecomm
Therapist
Training
Transportation
Truck Driver
Travel
Web
Work from Home

FREE SS SQL / BI OLAP Short Videos on YOUTUBE.com

Microsoft Community Contributor 2011 Microsoft Community Contributor 2012

Search SQLUSA FREE SQL Server Articles & FREE T-SQL Scripts

JOIN US ON TWITTER

Copyright 2005-2012, SMI Corp. All Rights Reserved.

SQL Server 2012 is a program product of Microsoft Corporation.
SQL Server 2008 is a program product of Microsoft Corporation.
SQL Server 2005 is a program product of Microsoft Corporation.
SQL Server 2000 is a program product of Microsoft Corporation.
 
SQL 2008 GRAND SLAM
 
SQL 2012 PROGRAMMING