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:
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.
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:
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:
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:
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:
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:
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:
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:
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:
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:
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.