Home » SQL Server Unique Key

SQL Server Unique Key

by Online Tutorials Library

SQL Server Unique Key

Unique key is a constraint in SQL Server. This constraint ensures that we cannot enter duplicate values into the columns. It means the data stored in the column is unique among the rows in the table. We can create a unique key on both single and multiple columns. For example, the student’s email addresses and roll numbers in the “student” table should be unique. In addition, the contact numbers of employees in the “Employee” table should also be unique.

The unique key allows inserting null values in the table. Although it accepts null values, SQL Server only allows one null value per column. It maintains the integrity of a column or set of columns used to store various values in a table.

Need of unique key

We use the unique key for the following reason:

  • It’s useful for preventing duplicate values from being stored in the column between two records.
  • It only keeps different values to ensure the database’s integrity and reliability when retrieving data in a structured manner.
  • It can also be used in conjunction with a foreign key for maintaining a table’s uniqueness.
  • It is possible for the table to have a null value.

How to create a unique key in a table?

SQL Server provides two ways to create a unique key in a table:

1. We can use the below syntax to create a single unique key column:

2. We can use the below syntax to create multiple unique key columns:

NOTE: It is advisable to provide the constraint name while creating a table. If we don’t provide a constraint name, SQL Server will generate its name automatically.

Example

Let us understand how to create a unique key in a table with the help of an example. The below statement creates a table named ‘student’ with a unique key:

Next, we will insert some records to understand how this constraint works:

Executing the statement will add records successfully into the table as no duplicates are found. Here is the output:

SQL Server Unique Key

Let’s insert another record into the table:

Executing the statement will throw an error as the ID column with value 1 is already inserted. Here is the output:

SQL Server Unique Key

Now, we will see how to define multiple unique key in a table. The following statement will create a unique key on multiple columns of the ‘student_info’ table:

Executing the statement will define a unique key for the columns roll number and email. We can verify it as below:

SQL Server Unique Key

How to add a unique constraint to an existing column?

SQL Server provides an ALTER command to add unique constraints in the existing column. When we add a unique key to an existing column or a set of columns in a table, SQL Server looks at the existing data in those columns to ensure that all values are unique. If it detects duplicate data, an error is generated, and the unique constraint is not added.

We can use the following syntax to add a unique constraint in a table:

Example

Suppose we have created a table named persons with the below statement:

After creating a table, we need to add a unique constraint to this table. In such a case, the following ALTER statement will add a unique constraint to the email column:

Similarly, the below statement adds a unique key on the phone column:

We can verify the unique constraint as below that shows uniq_email, and uniq_phone key is added successfully:

SQL Server Unique Key

How to drop a unique key from the table?

SQL Server uses ALTER TABLE statement to delete a unique key from the table. We can use the below syntax to drop a unique key:

Example

Suppose we want to remove the uniq_phone unique constraint from the person table. We can do this as below statement:

Verifying the table, we see that the uniq_phone is deleted successfully:

SQL Server Unique Key

How to modify the unique key?

SQL Server does not provide any direct query to modify a unique key. Therefore, if we want to modify the unique key, we must drop the key first and then recreate it to update it.

How unique key is different from the primary key?

We know that both unique and primary key constraints guarantee data uniqueness. However, we should use unique instead of primary key when we want to guarantee the uniqueness of a column or a set of columns that aren’t primary key columns. The main difference among them is that the primary key identifies each record in the table, and the unique key prevents duplicate entries in a column except for a NULL value. Here are some more differences:

  • A table cannot have more than one primary key in a relational database, but it can have multiple unique keys.
  • A primary key column cannot have NULL values. However, a unique key column can, although only one NULL per table is allowed.
  • A primary key should be unique, but a unique key cannot necessarily be the primary key.

The following example explains their differences where we insert records whose value in the email column is NULL:

Let us insert one more NULL value in the email column using the below query:

SQL Server throws an error message:

SQL Server Unique Key

Conclusion

This article will give a complete overview of unique key constraints. It explains how to use the SQL Server unique key to guarantee that the data in a column or group of columns is unique. In addition, how unique is different from the primary key.


Next Topic#

You may also like