Is primary key unique in database?

The primary key is the minimum set of traits that distinguishes any row of a table. It cannot have NULL and duplicate values. The primary key is used to add integrity to the table.

Ultimate Guide to Kickstart your GATE Exam Preparation
Download the e-book now

In the case of a primary key, both Duplicate and NULL values are not valid. And, it can be utilized as foreign keys for different tables.

What Is a Unique Key?

A unique Key is an individual value that is used to protect duplicate values in a column. The foremost purpose of a unique key in a table is to prevent duplicate values. However, when it comes to a unique value, the primary key also includes it. So, there is one big difference that makes a unique key different, and it is: a unique key can have a NULL value which is not supported in a primary key.

Difference Between Primary Key and Unique Key

ParametersPrimary KeyUnique KeyBasicThe primary key is accepted as a unique or sole identifier for every record in the table.When the primary key is not present in the table, then the unique key is also used as a unique identifier for recordsNULLIn the case of a primary key, we cannot save NULL values.In the case of a unique key, we can save a null value, however, only one NULL value is supported.PurposeIt supports entity integrity.It supports unique data.IndexThe primary key tends to generate a clustered index by default.The unique key tends to generate a non-clustered index.Number of KeyEach table holds just one primary key.A table can hold more than one unique key.Value ModificationWith the primary key, we cannot modify or delete the values.With the unique key, we can modify the column values.UsesIt is used to recognize specific records in the table.It anticipates storing duplicate entries in a column except for a NULL value.

Keep learning and stay tuned to get the latest updates on GATE Exam along with GATE Eligibility Criteria, GATE 2023, GATE Admit Card, GATE Syllabus for CSE (Computer Science Engineering), GATE CSE Notes, GATE CSE Question Paper, and more.

In the last blog, we have learned about Primary Keys and we saw how to find the primary key from a given relation. Apart from the primary key, there is one key called a unique key. Hold on, what is this unique key? Is it the same as that of primary key or there is some difference between these two? In this blog, we will learn about the unique key and we will also see the difference between primary key and unique key. So let's get started by revising the primary key.

Primary Key

The primary key is the minimal set of attributes which uniquely identifies any row of a table. The primary key cannot have a NULL value. It cannot have duplicate values. Primary key enforces the integrity of an entity type. Since a row represents a single instance of a table or entity, the primary key helps in enforcing the integrity of an entity type.

Example: We have a STUDENT table where Roll_no is the Primary key of the table. This Roll_no can be used to uniquely identify the rows of a table.

Note: A table can have only one Primary Key.

Unique Key

Unique Key is a key which has a unique value and is used to prevent duplicate values in a column. But, the primary key also has a unique value for each column. So, how is it different from a primary key? A unique key can have a NULL value which is not allowed in a primary key . But, if a unique key can have NULL values then how can it implement uniqueness in a column? By uniqueness, we mean that if we have a value present then either it would be unique or no value would be present at all. So, the main motive of a unique key in a table is to prevent duplicate values. To clearly understand this let us take an example.

Example: We have a STUDENT table.

In the above table, we have Phone_no as the Unique Key and Roll_no as the Primary Key. So, we can understand if any student has a Phone_no then it should be unique. Also, there might be some student who doesn't have any Phone_no .

Note: A table can have multiple Unique Key.

Difference between Primary Key and Unique Key

• Purpose: Primary Key is used to uniquely identify a row but a unique key is used to prevent duplicate values in a column.
• Existence : A table can have only one primary key but it can have multiple unique keys.

Example: In the Student Table, we have only one Primary Key i.e Roll_no but we have two Unique Key i.e Phone_no and Registration_no.

• Nullability: Primary key cannot have a NULL value but a Unique Key can have multiple NULL values.

Example: In the Student Table, Unique key Phone_no can have a NULL value whereas the Primary Key cannot have any NULL value.

• Modifiability: We cannot change the value of a primary key in a table but we can change the value of the unique key.
• Indexing: By default in the SQL servers, Primary Key creates a clustered index whereas the Unique Key creates the non-clustered index. In the clustered index, the data of the rows are sorted on the basis of their key values. In the non-clustered index, the data is stored at one location and the indices are stored at some other location.

Example: In the given Student table, the primary keys are sorted according to the key values and hence are they create a clustered index. The unique key like Phone_no and the Registration_no have the data stored in the physical files and the pointers are used to link the indices with the files.

Is a primary key Unique?

Can a primary key not be unique?

Is key unique in database?

Is secondary key unique?