Difference between Primary, Unique, Candidate and Composite Keys

By | August 24, 2017

Primary key:
Primary key is a key that is used for uniquely identify the each rows in a table. A primary key cannot be null and cannot be more than one primary key in a table. Where we create a primary key a cluster index is created. But we can change it with non- cluster index by update the create script of this table.

Example: – we can create ID as a primary key with cluster index

Unique key:
Unique key is a key used to ensure that data is not duplicated in two rows in a table. A Unique key can accept one null value and it can be more than one in a table. Where we create a Unique key a non-cluster index is created.

Example: – we can create ID is a primary key with cluster index.

Here we used MobileNo is a unique key because a mobile number cannot be duplicate that’s why we set it unique key for uniqueness. It is basically used if you want to select employee by Mobile Number then you ensure that it will one row only because a mobile number cannot be duplicate. Here we can also set ECODE or PINCODE as unique key because it cannot be duplicate.

Candidate key:
Candidate key is a key which can be treated as a primary key or unique key for uniquely identifies the each rows. Candidate key can be more than one in a table. In practical candidate key also known as unique key.

Example: – ECODE, MobileNo, PINCODE can be candidate key for primary key because they cannot be duplicate.

Composite key:
Composite key is a key which is the combination of two or more column that uniquely identifies the each rows.

Example: – suppose if there is no ECODE, MobileNo, PINCODE column in a table then we can make DOB+AGE+NAME as a composite key for uniquely identify the row. But it may be duplicate.

Superkey:

A superkey is a set of columns in a table for which there are no two rows that will share the same combination of values. So, the superkey is unique for each and every row in the table. A superkey can also be just a single column.

Read more about Superkey with brief example

 

I hope you will enjoy the difference between Primary, Unique, Candidate and Composite Keys. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

 

Like it? Share it