SQL SERVER – Difference Between Unique Index vs Unique Constraint

Unique Index and Unique Constraint are the same. They achieve same goal. SQL Performance is same for both.

Add Unique Constraint
ALTER TABLE dbo.<tablename> ADD CONSTRAINT
<namingconventionconstraint> UNIQUE NONCLUSTERED
(
<
columnname>
)
ON [PRIMARY]

Add Unique Index
CREATE UNIQUE NONCLUSTERED INDEX
<namingconventionconstraint> ON dbo.<tablename>
(
<
columnname>
)
ON [PRIMARY]

There is no difference between Unique Index and Unique Constraint. Even though syntax are different the effect is the same. Unique Constraint creates Unique Index to maintain the constraint to prevent duplicate keys. Unique Index or Primary Key Index are physical structure that maintain uniqueness over some combination of columns across all rows of a table. It is a convenient way to enforce a Unique Constraint for SQL Server.

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Constraint and Keys, SQL Index, SQL Scripts
Previous Post
SQL SERVER – Enable xp_cmdshell using sp_configure
Next Post
SQL SERVER – SELECT vs. SET Performance Comparison

Related Posts

56 Comments. Leave new

  • Liusha Abrosimov
    April 26, 2007 9:36 pm

    it is fun 2 read.
    i like it enjoyable. pls have more of this. i have subscribe.

    Reply
  • Realy nice one, i learnt new one

    Reply
  • Alex Bransky
    May 3, 2007 10:42 am

    From MSDN: “The index creation options other than FILLFACTOR that are available for a unique index are not available for a unique constraint.”
    So yes, their effects are the same, but there are differences.

    Reply
  • Thank you Alex,
    You are correct.

    Reply
  • Shiela Marie
    May 29, 2007 8:35 pm

    Very informative.

    I am new in thsi field, so may i ask.

    1. how could i make a query with the index.
    2. Is there ever a way where i can use this command in visual basic?

    thanks and more power

    Reply
  • “The index creation options other than FILLFACTOR that are available for a unique index are not available for a unique constraint.”

    Not true for 2005. Try this –

    create table Emp (Id int, Name varchar(20))
    GO
    ALTER TABLE Emp
    ADD CONSTRAINT IX_Emp
    UNIQUE (Name)
    WITH (IGNORE_DUP_KEY = ON)
    GO
    insert into Emp (Id, Name)
    select 1, ‘Me’
    union
    select 2, ‘Me’
    GO
    select * from Emp

    Reply
  • But I agree that no all options for an index are available for a constraint, such as DROP_EXISTING.

    Reply
  • You can use a column with Unique Constraint to be a foreign key for another table but you cant do that with Unique Index

    Reply
  • Another difference. A unique index can have nullable columns as part of the index. No column in a primary key can nullable.

    Reply
  • Creating a unique constraint implicitly creates a unique index on the table with the same name. Creating a unique index does not implicitly create a unique constraint.

    Like all constraints, Unique constraints have to be uniquely named within the database.

    Like all indexes, Unique indexes only have to be uniquely named within the table they owned by.

    Since creating the unique constraint also creates a unique index on the table, you cannot use the same name for a unique constraint and a unique index (or any constraint and any index for that matter) on the same table.

    Also, GPH is correct that -primary key- constraints can’t have nullable columns. However, Primary key constraints are not the same as unique constraints. Unique constraints can have nullable columns like unique indexes.

    Reply
  • hello,

    Plz tell me what is the difference between unique key and primary key

    also tell me how could i download the small date bank and sql at home because i am learning it in germany and also working in a company plz tell me how could i free download .

    Reply
  • Hello,

    FYI,

    In fact, unique key allows single nulls.

    E.g.

    ID-Name(AS UNIQUE KEY)
    1-Null
    2-Joyal
    3-Abhay

    Thanks.

    Regards,
    Joyal

    Reply
  • want to know, while inserting multiple records in a table under one transaction at what time a unique constraint and a unique index will check for uniqueness. As Books online suggests to create Unique constraint instead of Unique index, it gave me some idea, perhaps unique key checks uniqueness after every insert while unique index verifies after inserting all records. But i still want to know the functionality of the both.

    Reply
  • How can i add a unique cnstraint to table on
    combination of bit and varchar datatypes

    Thanks in advance

    Reply
  • Table_A:
    seqid (Primary Key)
    cmpno, prdno (Unique key)

    TABLE_B:
    bcompno, bprdno (Refers cmpno, prdno of TABLE_A)

    Can I have a Foreign Key relationship between TABLE_B and TABLE_A, using Unique Key, in SQL Server 2005?

    Reply
  • Can we create a uiique constraint without an uniqueindex when i am trying to drop a unique index which is created along with unique constraint it is giving error

    Reply
  • Surprisingly when I created unique index on one my tables it did not stop me to insert duplicates, while the unique constraint did the trick. Can you please explain why it happened?

    kind regards,

    Azim

    Reply
  • Darshan shah
    June 9, 2008 5:06 pm

    Hi Amar Kumar ,
    The main difference betn P.K and unique key is P.K. does not allow null value,where unique key allows null values.

    When P.K is created at tht time cluster index is created autmatically and when unique key is created at that time non clustr index is created.

    Reply
  • Darshan shah
    June 9, 2008 5:10 pm

    Hi Pinal ,

    the one more difference betn unique index and unique constraint is index is used to fetch the data fast. tht is use for improve the performance of fetching the data. where unique constraint is used to prevent the entering the redudant data in to the table.

    Reply
  • Darshan shah
    June 9, 2008 5:14 pm

    Hi Symphani,

    No u can’t use like that . bcz F.K is only reference to the P.K. Yes P.K is unique key but unique is not P.K.

    Reply

Leave a Reply