Difference Between Unique Key And Primary Key

In this blog, I'll talk about the differences between unique key and primary key in databases. Also learn similarities between Unique Key And Primary Key.

Primary keys and unique keys are commonly used in relational databases. In this blog, I will discuss the basics of Primary keys and Unique keys, the difference between a primary key and a unique key, and the similarities between the two.

So, let's first start from what are the similarities between these two and after that, the syntax, and by the end of this blog, you yourself will be able to differentiate between these two.

So, here we go.

Similarities between Primary Keys and Unique Keys
 

Sr. No.	UNIQUE KEY	PRIMARY KEY
1	A unique key is used to identify a record uniquely. Let me explain clearly. If you are making any column in the table as a unique key, you will be able to filter the data uniquely by using the unique key column name.	In the same manner, you can use the primary key to filter the data uniquely. It will give you the same result.

UNIQUE KEY
 

How to declare any column as a Unique key?

CREATE TABLE FirstBlog (
    ID int NOT NULL UNIQUE,
    LastName varchar(200) NOT NULL,
    FirstName varchar(200),
    Salary int
);

Desc

NOT NULL is a constraint. This means that the column ‘ID’ will not accept null values. By adding the UNIQUE constraint, we are making the ID column as the unique key, while the other column description will go as is. Like Lastname with varchar datatype will not accept NULL values but the column FirstName can accept NULL values as we have not marked any constraints and Salary will also accept NULL but integer in datatype.

Note. For the different types of constraints and datatypes in SQL servers, I will write another blog.

How to declare more than one column as a Unique key?

CREATE TABLE FirstBlog (
    ID int NOT NULL UNIQUE,
    LastName varchar(200) NOT NULL,
    FirstName varchar(200),
    Salary int,
    CONSTRAINT UC_FirstBlog UNIQUE (ID, LastName)
);

In the above representation of the declaration of the UNIQUE key on multiple columns, we can use the CONSTRAINT keyword. UC_ FirstBlog is the new name of the combination of two columns. One thing you can see in the above example is that we are able to declare two UNIQUE keys on the table FirstBlog.

How to add a UNIQUE key in an existing table.?

Let's take the above example with some corrections.

CREATE TABLE FirstBlog (
    ID int NOT NULL UNIQUE,
    LastName varchar(200) NOT NULL,
    FirstName varchar(200),
    Salary int,
    CONSTRAINT UC_FirstBlog UNIQUE (ID, LastName)
);

Execute it.

A table name with FirstBlog will be created in the database.

Now, if after that, we want to add a UNIQUE key on the ID column, then we need to ALTER the table. See below,

ALTER TABLE FirstBlog
ADD UNIQUE(ID);

In the same way, we can do this for multiple columns to make them a UNIQUE key. See below,

ALTER TABLE FirstBlog
ADD CONSTRAINT UC_FirstBlog UNIQUE(ID, LastName);

One more condition; if anyone wants to delete the UNIQUE key constraint on the table, we can use this syntax.

ALTER TABLE FirstBlog
DROP UNIQUE(ID);

PRIMARY KEY
 

How to declare a column as a Primary Key.?

CREATE TABLE FirstBlog (
   ID int NOT NULL PRIMARY KEY,
   LastName varchar(200) NOT NULL,
   FirstName varchar(200),
   Salary int
);

Desc

NOT NULL is a constraint which means column ‘ID’ will not accept null values, and by adding a PRIMARY KEY constraint, we are making the ID column as primary key and other column descriptions will go so on. ... Like Lastname with varchar datatype will not accept NULL values but the column FirstName can accept NULL values as we have not marked any constraints and Salary will also accept NULL but integer in datatype.

How to declare more than one column as the Primary Key?

CREATE TABLE FirstBlog (
    ID int NOT NULL,
    LastName varchar(200) NOT NULL,
    FirstName varchar(200),
    Salary int,
    CONSTRAINT PK_FirstBlog UNIQUE (ID, LastName)
);

In the above representation of the declaration of the Primary key on multiple columns, we can use the CONSTRAINT keyword. PK_ FirstBlog is the new name of the combination of two columns. Note that we can only create one primary key on a single table.

How to add a PRIMARY key in an existing table.?

Let's take the above example with some corrections.

CREATE TABLE FirstBlog (
    ID int,
    LastName varchar(200) NOT NULL,
    FirstName varchar(200),
    Salary int
);

Execute it.

A table name with FirstBlog will be created in the database.

Now, if after that we want to add a PRIMARY key on the ID column, then we need to ALTER the table. See below.

ALTER TABLE FirstBlog
ADD PRIMARY KEY (ID);

In the same way, we can make multiple columns as PRIMARY keys. See below.

ALTER TABLE FirstBlog
ADD CONSTRAINT PK_FirstBlog PRIMARY KEY(ID, LastName);

One more condition; if anyone wants to delete the PRIMARY key constraint on the table, we can use this syntax.

ALTER TABLE FirstBlog
DROP PRIMARY KEY;

Note. In the above example, we have not mentioned any column name for dropping the PRIMARY key because there is only one primary key allowed for the table.

Differences between Primary Keys and Unique Keys
 

Sr. No.	UNIQUE KEY	PRIMARY KEY
1	As the name suggests it is used to filter records with the unique ID but it accepts a NULL value.	A primary key is also used to filter records uniquely but doesn’t allow a NULL value
2	Creates a Non-Clustered Index.	Creates Clustered Index
3	Only one NULL value allowed	NULL values not allowed
4	Can create multiple UNIQUE keys on the table.	Only one primary key is allowed for a table

I tried to keep it simple and understandable.

Your comments, corrections, and appreciation, all are welcome.

Thank you!!