Unique Key in DBMS

The word 'unique' defines a thing which is unique from other things. A unique key in DBMS is a key that is able to identify all the records of a table uniquely.

Here, in this section, we will understand the unique key, its role and will also understand the working of the Unique key using some examples.

What is a Unique Key

A unique key in DBMS is used to uniquely identify a tuple in a table and is used to prevent duplicity of the values in a table.

Role of Unique Key

A unique key is used to remove the duplicity of values in a table. However, the usage of a primary key is the same, but there is a difference between both keys. A primary key cannot take a NULL value, but a unique key can have one NULL value as its value.

How Unique Key is different from Primary Key

There are the following difference points that will let us understand the difference between a Unique key and a Primary key:

• A primary key field can never have a NULL value as its value, whereas a unique key can have one of its field values as NULL.
• A table cannot have more than one primary key, but a table can have more than one unique key.
• There are different types of relational databases, but in some of these databases, the primary key can generate a clustered index by default. On the other hand, a unique key is able to generate a non-clustered index by default.

These explained some of the differences that will help us to differentiate between both keys.

Note: The SQL standards believe that the unique key does not satisfy or guarantees the uniqueness of those rows having NULL as its value. However, other RDBMS does not follow the SQL standards.

Implementing Unique Key

Here, we will implement the unique key constraint on:

CREATE Table

Below syntax shows the implementation of the unique key on CREATE Table:

Below is another syntax for implementing the unique key on multiple columns in a table:

We have set a unique key on two columns together while creating one table only.

ALTER Key

Below is the syntax to use the unique key on the ALTER key:

It is the syntax for defining a unique key for one column of a table.

Below is another syntax that can be used for defining unique key over multiple columns of a table:

Dropping Unique key

We can easily drop the unique key from the column/columns of a table.

Below is the syntax for dropping a unique key from a table:

Example of Unique Key

Let's see an example to understand the working of a unique key.

Consider a table STUDENT_DETAIL having the following attributes:

Roll_no: An attribute holding the roll number provided to the students.

Name: An attribute holding the name of the students on their specified roll numbers.

Address: An attribute holding the address of the student.

Personal_id: An attribute holding the number id of any other personal_id of the student such as Aadhar Card, Passport, etc.

The diagram of the above STUDENT_DETAIL is shown below:

Points to be noted:

1. From here, we can see that the Roll_no of the students will work as the Primary key and will contain no NULL value for it because every student has their unique roll number, and so the value cannot be null.
2. Secondly, we can set Personal_id as a unique key because there may be a student who may not have any other personal id because of some reason. So, for such a case, the value can be set as NULL, and only one value can be null for the unique key. So, we can set Personal_id as a unique key.

Also, from these two points, we can understand that both primary and unique keys are different from one another.

Example 2:

Another example we can take where we can have more than one unique key in just one table:

Consider an example where we have a Candidate_Detail table with the following attributes:

Candidate_no: An attribute holding the registration number of the candidate.

Name: An attribute holding the name of the candidate.

Aadhar_no: An attribute holding the 12-digit Aadhar number of the candidate.

Other_Id: An attribute holding the value of the other id number.

The below diagram shows the table creation for Candidate_Detail:

From the above table, we concluded that we could set Aadhar_no as well as Other_Id as the unique key because both of these columns will have either NULL or will carry a unique identification number. Thus, we can set two columns as a unique key in one table only.