52

I've got a column in a table (eg. UserName) which I want to make sure is unique. So I create a unique key for that column and call it IX_Users_UserName.

Now, if I do lots of searching for users based on their username I want to make sure there is an index for that field.

Do I need to create a separate index, or is the unique key also considered an index, just like the primary key is a clustered unique key?

casperOne
  • 72,334
  • 18
  • 180
  • 242
Pure.Krome
  • 82,011
  • 105
  • 379
  • 615
  • 2
    I feel the need to point out that a primary key is not automatically a clustered unique key. Your clustered index is not required to be on the primary key. – Tom H Jul 21 '10 at 19:23
  • Also see http://stackoverflow.com/questions/564895/what-is-the-difference-between-unique-key-and-index-with-isunique-yes – Darren Griffith Sep 20 '11 at 19:06

3 Answers3

40

Unique Key: Unique Key enforces uniqueness of the column on which they are defined. Unique Key creates a non-clustered index on the column. Unique Key allows only one NULL Value.

Alter table to add unique constraint to column:

ALTER TABLE Authors ADD CONSTRAINT IX_Authors_Name UNIQUE(Name) GO

Source

More information from MSDN.

FWIW -- if your constraint doesn't create an index, I would avoid naming it IX_ as that would typically be assumed to be associated with one (IX = Index).

tvanfosson
  • 509,016
  • 97
  • 693
  • 791
  • Cheers :) perfect answer. I'll also reanme all my unique keys to IX and drop the single index i had on those fields (why double up ... that's bad!). Cheers! – Pure.Krome Dec 14 '08 at 05:21
  • I like to prefix my unique indexes distinctively from non-unique indexes: UI or UIX. UK is also acceptable but does tell this is an index. – Jean Vincent Nov 08 '10 at 10:12
22

Basically, in SQL Server, a unique constraint is indeed realized by means of a unique index.

The differences between a UNIQUE constraint and a UNIQUE INDEX are quite subtle, really. If you create a UNIQUE INDEX, you can reference that in a foreign key constraints from another table (doesn't work if you create a UNIQUE constraint....).

So what's the difference? Well - a unique constraint really is more of a logical thing on a table - you want to express the intent that the contents of a given column (or group of columns) is unique.

A unique index (like most indices) is more of a "behind-the-scenes" implementation detail.

From my point of view, unless you really have a problem with it, I'd always use a UNIQUE INDEX - the benefit of being part of a referential integrity constraint is quite valid and can be very useful in certain cases. Functionally, in practice, there's no difference between using a Unique Constraint vs. Unique Index, really.

BenMorel
  • 31,815
  • 47
  • 169
  • 296
marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
1

A unique key is an index in I suspect almost every database product. It has to be, otherwise the database would have a hard time enforcing it: when you insert a value, the database has to answer, "does that value already exist?" The sane way to do that is consult an index.

I don't have a SQL Server in front of me to test, but I'd be shocked if it didn't.

derobert
  • 47,899
  • 11
  • 91
  • 122