SQL WORLD

What is difference between Primary key and unique key with examples?

In my previous article I have given the details about the primary key and unique key separately. There is always a question in interview about difference between primary key and unique key with examples. I would like to give you some important bullet points of difference between primary key and unique key with examples. We will see the details about primary key constraint and unique key constraint and then we will give you the difference between primary key and unique key with examples in tabular format.

What you will find in this article :

1.What is Primary key and Unique Key and its bullets.

2.Difference between Primary key and unique key

What is Primary key and Unique Key and examples :

Primary Key :

1.The primary key is nothing but the key which uniquely identifies the records in the table.

2.Primary key constraint is used to add integrity constraint to the table.

3.Primary key can not contains the null value. It means when you require null value to be accepted in the table then you can not use primary key over there.

4.Primary keys can be used as foreign key for other tables as well.

5.One table can contain only one primary key.

Example :

CREATE TABLE Student_Key

(

RollNo Number (10) PRIMARY KEY,

FName Varchar2 (15),

LName Varchar2 (15),

Location Varchar2 (10)

);

Unique Key :

1.Unique key constraint also used to identify the unique records from the table without adding the null values.

2.Unique key constraint is used when you require the multiple unique keys.

3.The unique key constraint may accept the null values.

4.Unique keys can not be used as foreign key.

5.You can have multiple unique key constraints for one table . So if you have requirement where you need multiple unique keys.

Example :

CREATE TABLE Student_unique

(

RollNo Number (10) UNIQUE,#

FName Varchar2 (15),

LName Varchar2 (15),

Location Varchar2 (20)

);

Difference between primary key and unique key in table :

Primary Key	Unique Key
Primary Key is nothing but unique identifier for every record for the table	Unique key is also unique identifier for record in the table
Only One primary key is defined for one table	Multiple unique keys can be defined for one table.
Primary key cannot contain null values	Unique key constraint can contain null values
The Unique clustered index can be created once you create primary key	Selection of unique key creates non clustered index
Example : CREATE TABLE Student_table ( RollNo Number (10) PRIMARY KEY, FName Varchar2 (15), LName Varchar2 (15) );	Example : CREATE TABLE Student_table ( RollNo Number (10) UNIQUE, FName Varchar2 (15), LName Varchar2 (15) );
Null value can not be inserted in above table with primary key	Null value can be inserted in table with unique key

Difference between primary key and unique key

The above article contains difference between primary key and unique key with examples. If you like this article or if you have issues with the same kindly comment in comments section.

Amit S

Oracle Consultant with vast experience in Oracle BI and PL/SQL Development. Amiet is the admin head of this website who contributes by preparing tutorials and articles related to database technologies. He is responsible to manage the content and front-end of the website.