Difference between Primary Key and Unique Key

In this article we look into the difference between Primary Key and Unique Key Constraints. We will see the description of both along with how to define a schema and create attributes in a table with these constraints.

Primary Key

A Primary Key is a key attribute or a column which is used to uniquely identify a row (tuple) in a table (relation). It maintains the integrity constraints of a table. A table can have only one primary key attribute. The data of primary key must be unique. Hence, no duplicate values are valid. Also, no NULL (empty) values are allowed during insertion as it violates the integrity constraint. The database automatically creates a Clustered Index over primary key which orders data on the basis of primary key.

We define a Primary Key Attribute as follows:

Column_Name DataType PRIMARY KEY

Unique Key

A Unique Key is a constraint or key attribute which also uniquely identifies a row in a table. It’s purpose is somewhat same as primary key. It differs in ways such as a table can have one or more than one Unique keys. It allows the possibility of NULL value insertion limited to only one null value insertion. In other words, Unique Key Constraints allow a column to have only one NULL value. No duplicate values are permitted. By default, the Unique key are generated in a Non-Clustered Index.

We define a Unique Key Attribute as follows while creating a table:

Column_Name Datatype UNIQUE

Now let us look at an example of creating a table with these constraints:

CREATE TABLE CARS
(
Car_Id INT PRIMARY KEY,
Car_Brand VARCHAR(10),
Car_NumberPlate VARCHAR(10) UNIQUE
);

Here Car_Id is the Primary key and Car_NumberPlate is the Unique Key of table CARS.

Let us assume we have these records in table CARS:

If we add a record like this:

INSERT INTO CARS VALUES(3,'Hyundai','');

This will add the record successfully since the unique key Car_NumberPlate allows one null value and show contents of database:

If we add any duplicate value in Primary key field Car_Id, it will show us error

INSERT INTO CARS VALUES(3,'Honda','TN056789');

So, when we try entering duplicate values to our Primary key Car_Id it shows the Unique constraint of the field is violated.

Also Read: Difference between Primary Key and Foreign Key