Javatpoint Logo
Javatpoint Logo

Difference between Primary Key and Unique key

Keys in MySQL are the column or set of columns used to construct a relationship between one or more than two tables. They are also used for accessing records from the table. Both keys provide a guaranteed uniqueness for a column or a set of columns in a table or relation. 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. In this article, we are going to compare essential differences between Primary and Unique Keys based on various parameters. Before making a comparison, we will discuss in brief these keys.

Primary Key vs Unique key

What is Primary Key?

The primary key is a unique or non-null key that uniquely identifies every record in that table or relation. The primary key column cannot store duplicate values that mean primary key column values are always unique. It is also called a minimal super key; therefore, we cannot specify more than one primary key in any relationship. A primary key column of one table can be referenced by a foreign key column of another table.

For example, we have a table named students with attributes such as Stud_ID, Roll_No, Name, Mobile, and Email.

Primary Key vs Unique key

Here only the Roll_No column can never contain an identical and NULL value. We know every student has a unique roll number. Therefore two students can never have the same roll number. This feature helps to identify each record in the database uniquely. Hence, we can make the Roll_No attribute a primary key.

Features of Primary Key

The following are the essential primary key features:

  • The primary key column cannot contain duplicate values.
  • The primary key implements the entity integrity of the table.
  • A table cannot have more than one primary key column.
  • We can make the primary key from one or more table fields.
  • The primary key column should have NOT NULL constraints.

What is a Unique Key?

The unique key is a single column or combination of columns in a table to uniquely identify database records. A unique key prevents from storing duplicate values in the column. A table can contain multiple unique key columns, unlike a primary key column. This key is similar to the primary key, except that one NULL value can be stored in the unique key column. The unique key is also called unique constraints and can be referenced by another table's foreign key.

For example, let's consider the same table named students with attributes such as Stud_ID, Roll_No, Name, Mobile, and Email.

Primary Key vs Unique key

Here Stud_ID can be assigned as a unique constraint because each student must have a unique identification number. If a student changes the college, then he or she would not have any student ID. In that case, the entry may contain a NULL value because a unique key constraint allows storing NULL, but it should be only one.

Features of Unique key

The following are the essential unique key features:

  • We can construct the unique key from one or more table fields.
  • A table can define multiple unique key columns.
  • By default, a unique key is in non-clustered unique indexes.
  • The unique constraint column can store NULL value, but only one NULL is allowed null per column.
  • The foreign key can reference the unique constraint in preserving the uniqueness of a table.

Key differences between Primary and Unique Key

The following points explain the key differences between primary and candidate keys:

  • A primary key can constitute one or more fields of a table to identify records in a table uniquely. On the other hand, a unique key prevents two rows from having duplicate entries in a column.
  • A table cannot have more than one primary key in a relational database, while there can be multiple unique keys per table.
  • A primary key column cannot contain NULL values, whereas a unique key can have NULL values, but only one NULL is allowed in a table.
  • A primary key should be unique, but a unique key cannot necessarily be the primary key.
  • The primary key by default is a clustered index where data is physically organized in the sequential index. In contrast, the unique key is a unique non-clustered index.
  • The primary key implements entity integrity, whereas the unique key enforces unique data.

Primary Key vs. Unique Key Comparison Chart

The following comparison chart explains their main differences in a quick manner:

Comparison Basis Primary Key Unique Key
Basic The primary key is used as a unique identifier for each record in the table. The unique key is also a unique identifier for records when the primary key is not present in the table.
NULL We cannot store NULL values in the primary key column. We can store NULL value in the unique key column, but only one NULL is allowed.
Purpose It enforces entity integrity. It enforces unique data.
Index The primary key, by default, creates clustered index. The unique key, by default, creates a non-clustered index.
Number of Key Each table supports only one primary key. A table can have more than one unique key.
Value Modification We cannot change or delete the primary key values. We can modify the unique key column values.
Uses It is used to identify each record in the table. It prevents storing duplicate entries in a column except for a NULL value.
Syntax We can create a primary key column in the table using the below syntax:
CREATE TABLE Employee
(
Id INT PRIMARY KEY, 
name VARCHAR(150), 
address VARCHAR(250)
)
We can create a unique key column in the table using the below syntax:
CREATE TABLE Person
(
Id INT UNIQUE, 
name VARCHAR(150), 
address VARCHAR(250)
)

Conclusion

In this article, we have made a comparison between primary key and unique key constraints. Here we have concluded that a unique key is useful when we want the columns don't contain duplicate values. And the primary key is useful when we do not want to keep NULL value in the table. It can also be ideal when we have a foreign key in another table to create a relationship among tables.






Youtube For Videos Join Our Youtube Channel: Join Now

Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA