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:
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:
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.
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:
Also, from these two points, we can understand that both primary and unique keys are different from one another.
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.