Unique Constraints in SQL Server

Unique Constraints specifies that two rows in a column cannot have the same value.  A Unique Constraint is helpful when you already have a primary key.

Consider the following facts before you implement a Unique Constraints

• Only one null value can appear in a column with a Unique Constraints.
• A table can have multiple Unique Constraints, where as it can have only a single primary key.
• A Unique Constraints is enforced through the creation of a unique index on the specified column or columns. This index cannot allow the table to exceed the 249 limit on non-clustered indexes.
• The database engine will return an error if you create an Unique Constraint on a column that contains data in which duplicate values are found.

When to use  UNIQUE Constraints:-

• The table  contains column that are  not part of the primary key but that, individually or as a unit, must contain unique values.
• Business logic dictates that the data stored in a column must be unique.