52

I have a table with a primary key, but I want two other columns to be constrained so the combination of the two is guaranteed always to be unique.

(a dumb example: In a BOOKS table, the IBAN column is the primary key, but the combination of the Title and Author columns should also always be unique.)

In the SQL Server Management Studio it's possible to either create a new Index and set IsUnique to Yes, or I can create a new Unique Key.

What is the difference between the two approaches, and which one suits best for which purposes?

JacobE
  • 8,021
  • 7
  • 40
  • 48
  • 5
    Just in case relevant: if you need to allow multiple rows to be NULL but any non-NULL rows must be UNIQUE : either enforce that with a Trigger, or a VIEW with a UNIQUE INDEX - CREATE VIEW xxx AS SELECT Title, Author FROM Books WHERE (Title + Author) IS NOT NULL then create index on that View – Kristen Feb 19 '09 at 21:39
  • Thanks Kirsten, that was useful information! – JacobE Feb 26 '09 at 13:00
  • 1
    Also see http://stackoverflow.com/questions/366186/is-the-sql-server-unique-key-also-an-index – Darren Griffith Sep 20 '11 at 19:07

6 Answers6

36

Creating a UNIQUE constraint is a clearer statement of the rule. The IsUnique attribute of the index is an implementation detail - how the rule is implemented, not what the rule is. The effect is the same though.

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • 1
    @pylover: in some cases a constraint (e.g. a foreign key or not null) can improve performance by giving the optimizer useful information it would not otherwise have. In the case of uniqueness I'd imagine there is no difference between a constraint and just an index, but not 100% sure. – Tony Andrews Sep 10 '12 at 09:36
8

There is a clear difference between the 2. A unique constraint defines what combination of columns has to be unique. A unique index is just a way of making sure the above is always valid. But it's possible to have a non-unique index supporting a unique constraint. (if the constraint is deferable = Only has to be valid at commit time but is allowed to be broken in the middle of a transaction)

6

Just so that you know, when you create a unique constraint SQL Server will create an index behind the scenes

SQLMenace
  • 132,095
  • 25
  • 206
  • 225
4

One thing I just found out the hard way is that in SSMS scripting of unique keys was set to true by default but the scripting of indices was set to False. When I used the Script Table As context menu from SSMS I didn't get my unique indices.

Also if the type is set to Unique Key, you can't change the "Ignore Duplicate Key" setting. First you have change the type from Unique Key to Index then you can set Ignore Duplicate Keys to true.

Tod
  • 8,192
  • 5
  • 52
  • 93
0

I do not think there is any difference between them but using unique index , we can have two benefits , as the column is already unique and also had the index on it so i gonna be more faster to search . So using unique index is more benefit.

Bibash Adhikari
  • 163
  • 2
  • 4
  • 22
0

unique indexes are unique keys.

tehvan
  • 10,189
  • 5
  • 27
  • 31