Unique Key in SQL Server

Unique Constraint or Unique key in SQL Server enforces the uniqueness of the column value. i.e. no two rows of a table can have the same values. In this tutorial, we will learn how to create a Unique Constraint, how to drop it, how to add a Unique key to an existing table, etc.

Need for Unique Constraint

The SQL Server Primary Key Constraint enforces the uniqueness of the column ( or group of column) values. But we can create only one Primary key per table.

In the following example, we make EmployeeID as Primary Key. But what if we want EmailID to be unique. This is where we use the Unique constraint.

When we add a UNIQUE constraint to an existing column or columns in the table, the SQL Server ensures that all values in the column (or columns) are unique. It does so by creating a non clustered index on those rows.

Create Unique Constraint

Now, when you try to insert a duplicate value into the EmailID column, the SQL Server throws a Violation of UNIQUE KEY constraint error.

Violation of UNIQUE KEY constraint UQ__Employee__7ED91AEE70A8B9AE. Cannot insert duplicate key in object dbo.Employee.

Composite Unique Key

You can also create a Composite Unique Key consisting of two or more fields. To do that we need to apply the Unique Constraint on the table level.

In the following example, we create a unique key consisting of FirstName & LastName.

The FirstName & LastName themselves can contain duplicate values.

But, Both together should not contain any duplicate values

The following example creates two separate Unique key constraints one for each FirstName & LastName.

Naming the Unique Key

The following queries show how you can give a name to Unique Constraint.

Finding the Unique Constraints in the Table

Use the following query to find out all the Unique Constraints with their names & column names.

Dropping the Unique Key

Dropping a Unique Key constraint is similar to dropping any other constraint. First, find out the name of the unique constant using the query from the previous section and use the Alter table Drop constraint query to delete the Unique key

Adding Unique Constraint on Existing Table

The following query adds a Unique Constraint to an existing table.

The SQL Server will check the Uniqueness of existing values before creating the Unique Constraint. If the column to which we apply unique constraint has duplicate values, the SQL Server returns an error and does not add the constraint

You can also create the constrain without naming it.

Note that running the above query multiple times will result in multiple constraints on the same fields. That is the reason why you must name the constraint using a naming convention, which will prevent the duplication of constraints.

Rules & Limitations

Unique Key on Nullable columns allowed

UNIQUE constraints allow for the value NULL. However, as with any value participating in a UNIQUE constraint, only one null value is allowed per column

You can insert a NULL value into the EmailID field

But inserting another NULL in EmaildID field will result Violation of UNIQUE KEY constraint error

You can use Unique Key in a Foreign Key constraint

A UNIQUE constraint can be referenced by a Foreign key constraint. The following example, EmailID field of tableA is set as Foreign Key referencing the EmailID of Employee Table

Unique Key & Index

The SQL Server creates a NON CLUSTERED index to enforce the UNIQUE constraint. You can change that behavior and create the CLUSTERED index instead. Since a table can have only one CLUSTERED index, the primary key index will automatically become a NONCLUSTERED index

Reference

Create Unique Constraints

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top