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 KeyUnique Key
Primary Key is nothing but unique identifier for every record for the tableUnique key is also unique identifier for record in the table
Only One primary key is defined for one tableMultiple unique keys can be defined for one table.
Primary key cannot contain null valuesUnique key constraint can contain null values
The Unique clustered index can be created once you create primary keySelection 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 keyNull 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.

Recent Posts

The Essential Guide to Cryptocurrency Exchange Platform Development for Beginners and Beyond

Introduction Cryptocurrencies took the world by storm, setting up a new financial system and breaking…

5 months ago

Top 20 System Administrator Interview Questions and answers

In my previous article I have given Top 20 technical support interview questions with its…

8 months ago

Desktop Support Scenario Based Interview Questions

In my previous articles I have given 15 most asked desktop support interview questions with…

8 months ago

How Do Business Analysts Use SQL for Their Needs?

A business analyst is someone who is versed in processes of data analysis used for…

8 months ago

Top 15 Control-M Interview Questions with Answers

In my previous article I have already given top questions and answers for Desktop support…

8 months ago

Top 20 SQL Interview Questions for Production Support

In my previous article I have given unix production support interview questions and answers. In…

8 months ago