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 (http://blog.SQLAuthority.com)

About these ads

55 thoughts on “SQL SERVER – Difference Between Unique Index vs Unique Constraint

  1. 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.

  2. 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

  3. “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

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

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

  6. 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.

  7. 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 .

  8. 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.

  9. 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?

  10. 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

  11. 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

  12. 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.

  13. 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.

  14. 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.

  15. Hi Pardeepe,

    U can define unique key on varchar datatype but u can’t do it on bit datatype bcz bit datatype has values 0,1,null .

  16. please explain how the value ‘a’ and ‘a ‘ are treated the same in SQL Server 2005 when creating indexes:

    create table a(b nvarchar(5))
    insert into a values (‘a’)
    insert into a values (‘a ‘) — please note space at the end
    create unique index a1 on a(b)

    gives error:

    Msg 1505, Level 16, State 1, Line 2
    The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.a’ and the index name ‘a1′. The duplicate key value is (a ).

  17. Darshan,
    I’m afraid that is incorrect. A foreign key may reference the primary key of the table *or* any column(s) defined as unique.

    Tushar,
    In Sql Server, any trailing spaces are ignored when comparing strings. I assume this is so that if a field is defined “char(10)” and another is defined “varchar(10)” and you insert ‘a’ into each one, then CharField = VarCharField will evaluate to TRUE even though the char(10) field actually contains an ‘a’ followed by 9 spaces.

    So in statements like “where RTrim(CharField) = RTrim(VarCharField)”, the RTrim is not necessary. Even Len(CharField) and Len(VarCharField) will return 1 for both. However, leading spaces *are* significant.

  18. TommCatt

    Thankx for your reply.

    Your explanation is correct.

    As mentioned by you, I am aware of SQL Server’s handling of various string data types.

    In my opinion, ignoring trailing blanks while comparing string objects (aspecially when they are defined as variable length strings is a bad design flow).

    I have designed a data warehouse where we are bringing data from Oracle whose table’s have key values e.g. AB and AB. Again having space in a key field itself is a bad design but structurally Oracle treats AB and AB as different values (which infact they are) and handles them OK.

    When I bring then over to SQL server, I get unique key violation … vola … :)

    My work around is to replace trailing blanks in key fields with ~ character. Again a temporary fix till SQL Server provides a way to have sound string data type comparisons.

    Again, Thankx for your reply.

    • Tushar,

      Having same Oracle –> SQL Server conversion problem. Its only to be maintained for historical reporting so your work around is perfect!
      I tried ANSI_PADDING ON (at db level and at table create and insert time) but no luck and gave up.

      “My work around is to replace trailing blanks in key fields with ~ character. Again a temporary fix till SQL Server provides a way to have sound string data type comparisons”

      A container to hold 6 beers at the same time! BRILLIANT!!

      THX
      FC Wright

  19. i need to know how work when there is a situation that there is a Unique Key and this field “alow null”, but when i am going to create a Unique Key the SQLSERVER saw that there were values duplicated and the values are “nulls”. How do i sove this problem?

  20. @Cristiano,

    As you may be aware, Unique Key can allow only one null. Meaning only one null can be accepted not more than that. If you have more than one null on any column, you cannot make that column/field as unique key.

    I am sure there must be another way of doing this, I would do something like this,

    1. Since there is no way you can make that field a unique key, how about making a composite key.

    a) Combine this field with any other unique column/field and make a composite unique key. If any other unique key already exists then add this field to the previous unique key.

    b) if you do not have any unique key ( I am sure you do), but if you dont, then you can do this always, using Enterprise manager or SSMS object Explorer, create a new Identity column in the table and then create a unique composite key on these two field. ( You can create an identity column if you have data in the table, you DONT have to drop and recreate the table, using EM or SSMS you can always create a identity column in the table).

    If you consider case a), then make sure there are no duplicates for the combination of new field and the field you chose for unique key.

    If you create a composite unique key on cola and colb combined then you can have more than one null in cola and in colb, but again the combination of cola and colb should be unique.

    Once you start making a composite unique key… this concept will be more easier.

    Try doing this, either in Enterprise Manager or SSMS ( Object Explorer) much easier in interface than executing scripts.

    Hope this helps.
    Imran.

  21. Pingback: SQL SERVER - Explanation about Usage of Unique Index and Unique Constraint Journey to SQL Authority with Pinal Dave

  22. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  23. Just discovered another difference between declaring a unique index vs. a unique constraint.

    SQL will raise an error with an error code of 2601 when violating a unique index, while it will use error code 2627 for violating a unique constraint.

    Minor difference, but while they are very similar, they are NOT the same.

  24. @Reed

    Very interesting!

    CREATE TABLE A(A INT UNIQUE);
    INSERT INTO A(A) VALUES(1);
    INSERT INTO A(A) VALUES(1);
    DROP TABLE A;

    CREATE TABLE A(A INT);
    CREATE UNIQUE INDEX AA ON A(A);
    INSERT INTO A(A) VALUES(1);
    INSERT INTO A(A) VALUES(1);
    DROP TABLE A;

    • I think you can’t obtain these values direct from sql server, probably you can do using the system tables.
      I did this in .net C# via OLEDB
      Above the code:

      using System;
      using System.Collections;
      using System.Data;
      using System.Data.OleDb;

      namespace namespace_name {
      public class class_name {
      private string table_name = “tabela_teste”;
      private string var_OleDbConnection = “Provider=sqloledb;Data Source=127.0.0.1;Initial Catalog=database_name;User Id=sa;Password=;”;
      #region public indices_colunas[] indexes
      private indexes_columns[] Indices;
      public indexes_columns[] indexes {
      get {
      if (Indices == null) {
      ArrayList ar = new ArrayList();
      DataView dv = GetOleDbSchema(System.Data.OleDb.OleDbSchemaGuid.Indexes, new object[] {null, null, null});
      for (int i = 0; i < dv.Count; i++) {
      if (dv[i]["TABLE_NAME"].ToString().Trim().ToLower() == table_name.ToLower()) {
      if (dv[i]["TABLE_NAME"].ToString().Trim().ToLower() == table_name.ToLower()) {
      indexes_columns ind = new indexes_columns();
      ind.table_name = dv[i]["TABLE_NAME"].ToString();
      ind.column_name = dv[i]["COLUMN_NAME"].ToString();
      ind.index_name = dv[i]["INDEX_NAME"].ToString();
      ind.primary_key = Convert.ToBoolean(dv[i]["PRIMARY_KEY"]);
      ind.clustered = Convert.ToBoolean(dv[i]["CLUSTERED"]);
      ind.unique = Convert.ToBoolean(dv[i]["UNIQUE"]);
      ar.Add(ind);
      }
      }
      }
      Indices = new indexes_columns[ar.Count];
      ar.CopyTo((indexes_columns[]) Indices);
      }
      return Indices;
      }
      }
      #endregion
      #region public struct indexes_columns
      public struct indexes_columns {
      public string table_name;
      public string column_name;
      public bool clustered;
      public bool unique;
      public bool primary_key;
      public string index_name;

      }
      #endregion
      private DataView GetOleDbSchema(Guid SchemaGuid, object[] parametros) {
      DataView dv = new DataView();
      OleDbConnection myConnection = new OleDbConnection(var_OleDbConnection);
      try {
      myConnection.Open();
      dv = new DataView(myConnection.GetOleDbSchemaTable(SchemaGuid, parametros));
      }
      catch {
      throw;
      }
      finally {
      myConnection.Close();
      }
      return dv;
      }
      }
      }

    • To retrieve “Unique Constraints” :
      sp_helpconstraint MyTable,’nomsg’

      To retrieve “Unique Index”:
      select name, type_desc, is_unique, is_primary_key
      from sys.indexes
      where object_id = object_id(‘MyTable’)

      Igor

  25. Hi i need a table as follows

    ID, Accno, Type
    1 , 1 , 1
    1 , 1, 2
    1, 1, 3
    1 , 2 , 1 it can not be allowed
    2 , 1, 1 it can be allowed
    how can i create this type of table?
    ID cannot be used for any Other Account Number but it can be used multiple times for same Account Number??

    Thanks in advance
    Madhukumar N

    • CREATE TABLE dbo.Madhu(
      ID int NOT NULL,
      AccNo int NOT NULL,
      Type int NOT NULL,
      PRIMARY KEY (ID,AccNo,Type));

      go

      CREATE VIEW dbo.MadhoIDAccNo WITH SCHEMABINDING AS
      SELECT ID, AccNo, COUNT_BIG(*) c
      FROM dbo.Madhu
      GROUP BY ID, AccNo;

      go

      CREATE UNIQUE CLUSTERED INDEX OnlyOneAccNoPerID ON dbo.MadhoIDAccNo(ID);

  26. You can Include a column in a (Unique) Index. Although this extra column (p.e. a primary key) can always make this index Unique it does not affect the uniqueness of the other columns.
    So If you have a identity seed table with a PK on the ID and you create a Unique Index on column 2 and 3 and INCLUDe the PK column in the index duplicate values of column 2 & 3 will still be noticed (the inserts fails or when IGNORE_DUP_KEY = ON it is ignored)

  27. I haven’t tried any of this yet,but it seems that another difference between a unique index and a unique constraint would be the ability to use the optional INCLUDE clause as part of the CREATE INDEX statement to include columns that are not part of the actual unique index. It would probably be overkill, but it seems that you could have both a unique index and a unique constraint and thereby get the benefits of both.

  28. Cannot connect to sql server 2005 management studio express on local pc using windows authentication? & SQL Configuration tool is also not available with my setup…
    cannot connect to sql server 2005 management studio express on local pc using windows authentication?
    It’s unable connect to SQL server 2005 management studio express on local m/c using windows authentication. I went through microsoft support script but it also cann’t wok as there is no configuration manager installed with setup (43.2 MB). If there is pb in setup files or something else….
    while connection it shows error report is as Follows

    TITLE: Connect to Server
    ——————————

    Cannot connect to pran-pun-srv-trup.

    ——————————
    ADDITIONAL INFORMATION:

    An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

  29. There are differences in the DROP statements as well:

    – UNIQUE CONSTRAINT
    ALTER TABLE DROP CONSTRAINT

    – UNIQUE INDEX
    DROP INDEX ON

  30. Pingback: SQL SERVER – Weekly Series – Memory Lane – #026 | SQL Server Journey with SQL Authority

  31. I realize this is an old thread but wouldn’t an unique index also potentially participate in a query plan where as a constraint would only be used to check on inserts/updates? My guess is statistics and such would help performance of the index version and keep it in memory where as the constraint version wouldn’t necessarily live in ram at startup/move in and out similar to a “hot” index being hit by lots of queries.

  32. There is no difference between Unique Index and Unique Constraint. So, My question is if unique key column allow single “NULL” values insertion then is it possible to insert single “NULL” values inside unique indexed column?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s