What are unique key constraints?
A unique constraint is a rule that restricts column entries to unique. In other words, this type of constraints prevents inserting duplicates into a column. A unique constraint is one of the instruments to enforce data integrity in an SQL Server database. Since a table can have only one primary key, you can use a unique constraint to enforce the uniqueness of a column or a combination of columns that do not constitute a primary key.
Creating a unique constraint on a column automatically creates a unique index. This way SQL Server implements the integrity requirement of the unique constraint. Therefore, when attempting to insert a duplicate value into a column, on which a unique constraint is defined, the Database Engine will detect the unique constraint violation and issue a corresponding error. As a result, the row with the duplicate values will not be added to a table.