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
  • 73,706
  • 19
  • 184
  • 253
Pure.Krome
  • 84,693
  • 113
  • 396
  • 647
  • 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
  • 524,688
  • 99
  • 697
  • 795
  • 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
  • 1
    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
  • 34,448
  • 50
  • 182
  • 322
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
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
  • 49,731
  • 15
  • 94
  • 124