What is the difference between Unique Key and Index with IsUnique=Yes?

JacobE_30056 0
Viewed 13941

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?

sql-server database
6 Answers

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.

user_187470

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)

Admin1

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

user_7400

unique indexes are unique keys.

tehvan_618480

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.

user_68989800
Related