Best IT Recipes

What is the difference between a unique key and an index with IsUnique = Yes?

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

(stupid example: in the BOOKS table, the IBAN column is the primary key, but the combination of the Title and Author columns must also always be unique.)

In SQL Server Management Studio, you can either create a new index, or set IsUnique to Yes, or I can create a new unique key.

What is the difference between the two approaches and which one is better suited for which purpose?

+50
database sql-server
Feb 19 '09 at 11:39
source share
6 answers

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

+35
Feb 19 '09 at 11:47
source share

There is a clear difference between the two. A unique constraint determines which combination of columns should be unique. A unique index is just a way to make sure the above is always true. But it is possible to have an imperfect index supporting a unique constraint. (if the restriction is deferred = only should be valid at the time of commit, but it is allowed to interrupt it in the middle of the transaction)

+7
Feb 19 '09 at 21:26
source share

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

+6
Feb 19 '09 at 13:59
source share

One thing that I just saw is that by default SSMS for unique key scripts was set to true, but for index scripts it was set to False. When I used the Script Table As context menu from SSMS, I did not get my unique indexes.

In addition, if the type is set to Unique Key, you cannot change the Ignore Duplicate Key option. First you change the type from a unique key to an index, then you can set Ignore duplicate keys to true.

+4
Apr 19 '12 at 21:45
source share

Unique indexes are unique keys.

0
Feb 19 '09 at 11:42
source share

I don’t think there is any difference between them, but using a unique index, we can get two advantages, since the column is already unique and also contains an index, so the search will be faster. Thus, the use of a unique index is more beneficial.

0
Dec 21 '18 at 2:30
source share


More articles:


All Articles