When I want a column to have distinct values, I can either use a constraint

create table t1(
id int primary key,
code varchar(10) unique NULL

or I can use a unique index

create table t2(
id int primary key,
code varchar(10) NULL

create unique index I_t2 on t2(code);

Columns with unique constraints seem to be good candidates for unique indexes.

Are there any known reasons to use unique constraints and not to use unique indexes instead?

  • 18
    are they actually different? I think in some databases e.g. postgresql, a unique constraint simply creates a unique index. I'm not answering because I know nothing about sql server. Jan 4, 2011 at 11:10
  • 7
    in postgresql, you can use an expression in a unique index but not in a unique constraint. Dec 16, 2013 at 22:35
  • 2
    On MS SQL, they're implemented the same. Try creating two tables with the same data, one with a unique constraint, the other with a unique index. They will use the same amount of index space, and both will be able to seek against the unique index which (in practice) is created either way. Apr 16, 2015 at 20:12

6 Answers 6


Under the hood a unique constraint is implemented the same way as a unique index - an index is needed to efficiently fulfill the requirement to enforce the constraint. Even if the index is created as a result of a UNIQUE constraint, the query planner can use it like any other index if it sees it as the best way to approach a given query.

So for a database that supports both features the choice of which to use will often come down to preferred style and consistency.

If you are planning to use the index as an index (i.e. your code may rely on searching/sorting/filtering on that field to be quick) I would explicitly use a unique index (and comment the source) rather than a constraint to make that clear - this way if the uniqueness requirement is changed in a later revision of the application you (or some other coder) will know to make sure a non-unique index is put in place of the unique one (just removing a unique constraint would remove the index completely). Also a specific index can be named in an index hint (i.e. WITH(INDEX(ix_index_name)), which I don't think is the case for the index created behind the scenes for managing uniqueness as you are unlikely to know its name.

Likewise if you are only needing to enforce uniqueness as a business rule rather than the field needing to be searched or used for sorting then I'd use the constraint, again to make the intended use more obvious when someone else looks at your table definition.

Note that if you use both a unique constraint and a unique index on the same field the database will not be bright enough to see the duplication, so you will end up with two indexes which will consume extra space and slow down row inserts/updates.

  • 4
    @jae: a DBMS certainly could be bright enough, but you'd have to check with each DBMS to see if it is. If you ask MSSQL to create two identical indexes it will create two rather than one referred to by two names (at least this was the case last time I spotted a situation like that (due to a copy+paste error on my part)), so I assume the same is the case if one of the indexes is present due to a constraint. Jan 4, 2011 at 12:51
  • 4
    +1 @David Spillett I think that basically the DBMS just assumes you know what you are doing; If you feel like creating the same index twice, it doesn't question you on that. Jan 11, 2011 at 22:32
  • 2
    very insightful. Do you happen to know if this behavior is in MySQL and Apache Derby as well?
    – corsiKa
    May 18, 2011 at 15:23
  • 6
    You can name a constraint and use it in an index hint. CREATE TABLE #T(X INT CONSTRAINT PK PRIMARY KEY NONCLUSTERED);SELECT * FROM #T WITH(INDEX(PK)) WHERE X = 1. Indexes can be more flexible though in that constraints don't support all index options such as INCLUDEd columns or filtered indexes. Feb 8, 2013 at 11:22
  • 2
    "…unique constraint is implemented the same way as a unique index". No, it isn't. For example, unique indexes can be disabled, unique indexes can be filtered. Dec 16, 2013 at 21:10

In addition to the points in other answers, here are some key differences between the two.

Note: The error messages are from SQL Server 2012.


Violation of a unique constraint returns error 2627.

Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'P1U_pk'. Cannot insert duplicate key in object 'dbo.P1U'. The duplicate key value is (1).
The statement has been terminated.

Violation of a unique index returns error 2601.

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.P1' with unique index 'P1_u'. The duplicate key value is (1).
The statement has been terminated.


A unique constraint cannot be disabled.

Msg 11415, Level 16, State 1, Line 1
Object 'P1U_pk' cannot be disabled or enabled. This action applies only to foreign key and check constraints.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.

But the unique index behind a primary key constraint or a unique constraint can be disabled, as can any unique index. Hat-tip Brain2000.


Note the usual warning that disabling a clustered index makes the data inaccessible.


Unique constraints support indexing options like FILLFACTOR and IGNORE_DUP_KEY, though this hasn't been the case for every versions of SQL Server.

Included Columns

Nonclustered indexes can include non-indexed columns (termed a covering index, this is a major performance enhancement). The indexes behind PRIMARY KEY and UNIQUE constraints cannot include columns. Hat-tip @ypercube.


A Unique constraint cannot be filtered.

A unique index can be filtered.

ON dbo.Students6( DriversLicenceNo ) WHERE DriversLicenceNo is not null ;

Foreign Key Constraints

A Foreign Key constraint cannot reference a filtered unique index, though it can reference a non-filtered unique index (I think this was added in SQL Server 2005).


When creating constraint, specifying a constraint name is optional (for all five types of constraints). If you don't specify a name then MSSQL will generate one for you.

    TID int not null PRIMARY KEY
) ;
    TID int not null CONSTRAINT T2_pk PRIMARY KEY
) ;

When creating indexes, you must specify a name.

Hat-tip @i-one.




  • A unique constraint can be disabled and enabled via the same method as an index: ALTER INDEX tbl ON uconstraint DISABLE, ALTER INDEX tbl ON uconstraint REBUILD
    – Brain2000
    Jul 19, 2018 at 15:03
  • Thanks @Brain2000. Coincidentally, I taught a section on disabling indexes this morning just before I read this comment. Jul 20, 2018 at 1:58

To quote MSDN as an authoritative source:

There are no significant differences between creating a UNIQUE constraint and creating a unique index that is independent of a constraint. Data validation occurs in the same manner, and the query optimizer does not differentiate between a unique index created by a constraint or manually created. However, creating a UNIQUE constraint on the column makes the objective of the index clear... more info here


The Database Engine automatically creates a UNIQUE index to enforce the uniqueness requirement of the UNIQUE constraint. Therefore, if an attempt to insert a duplicate row is made, the Database Engine returns an error message that states the UNIQUE constraint has been violated and does not add the row to the table. Unless a clustered index is explicitly specified, a unique, nonclustered index is created by default to enforce the UNIQUE constraint... more info here

Other into: https://technet.microsoft.com/en-us/library/aa224827%28v=sql.80%29.aspx


One of the major differences between a unique constraint and a unique index is that a foreign key constraint on another table can reference columns that make up a unique constraint. This is not true for unique indexes. In addition, unique constraints are defined as part of ANSI standard, while indexes are not. Finally, unique constraint in considered to live in the realm of logical database design (which may be implemented differently by different DB engines) while index is physical aspect. Therefore, unique constraint is more declarative. I'd prefer unique constraint in almost all cases.

  • 11
    -1 In SQL Server the following is wrong: "a foreign key constraint on another table can reference columns that make up a unique constraint. This is not true for unique indexes". In SQL Server, we can refer FK constraints to unique indexes.
    – A-K
    May 2, 2012 at 20:31
  • 4
    The ability for a foreign key constraint to reference a unique index was, I think, added in SQL Server 2005. Many sources, including some pages in BOL, haven't been updated to reflect the changes, so I don't think Dmitry's answer deserves the downvotes. The rest of his answer is spot on - Constraints are ANSI-standard, indexes are not. Dec 16, 2013 at 21:08
  • Standards are important. If Ansi standards is to use a Unique Constraint, then we should use a Unique Constraint.
    – Rhyous
    Sep 19, 2017 at 17:56

In Oracle a major difference is you can create a function-unique index, which is not doable with unique constraints:

For example

create unique index ux_test on my_table (case when amount != 0 then fk_xyz end);

So fk_xyz is only unique for record which have amount != 0.

  • 8
    In SQL Server (the question's tag), indexes can be filtered with a WHERE clause. CREATE UNIQUE NONCLUSTERED INDEX P4_U ON DBO.P4 ( PID ) WHERE TXT = 'qwert' ; Dec 16, 2013 at 21:23

UNIQUE Constraint is preferred over UNIQUE Index. When the constraint is not unique you need to use a regular or non unique index. Constraint is also another type of index. Index is used for faster access.

Unique Indexes can have where clauses. For example, you can create indexes for every year based on the date column

WHERE Sale_Date BETWEEN '2012-01-01' AND '2012-12-31'
  • 3
    "Constraint is also another type of index." No, it isn't. Some constraints (PK, UQ, FK) can be and are often enforced by the use of indexes. Not necessarily though and not by default in all DBMS. Feb 8, 2017 at 10:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.