SQL: Major difference between primary key, foreign key, and unique key -

In SQL key constraints play a very important role. Sometimes you might be confused between these three key constraints. Don’t worry our today’s topic is very short and problem-solving for your confusion.

So let’s start…

Primary Key:

Primary always be a unique key.
The primary key never contains a null value or it is by default, not null constraints.
There is only one primary key in the table.
But primary key consists of more than one column. It means there is a possibility that a primary key is made of more than one column but it is only one in the table.

Examples:

The primary key with only one column:

create table mytable(
id int not null primary key,
name varchar (50) not null,
city varchar (50) not null
);

The primary key with multiple columns:

create table mytable(
id int not null,
last_name varchar (50) not null,
name varchar (50) not null,
city varchar (50) not null,
constraint new_id primary key (id, last_name)
);

In the above example, we created a new id name as “new_id” which is the merger of two columns “id and last_name”, and set it as a primary key.

Note that, we can also alter or drop the primary key. For example, if we forget to set id as the primary key so we have to use following query for alter our table:

alter table mytable
add primary key (id);

Same as above, if we want to delete the primary key constraint. we have to use the following query for that:

alter table mytable
drop primary key;

In the above example, we do not use any column name like id because there is only one primary key in a table.

Foreign Key:

Mainly foreign keys used to link or join two or more tables.
There is more than one foreign key on a table.
It may be a null constraint.
The foreign key of a table is the reference of the primary key of another table.
The foreign key field only contains the data of its reference key.
A foreign key is not a unique key. It means a foreign key contains duplicate values.

Example:

create table mytable2(
id int,
pin int not null,
foreign key (id) references mytable(id)
)

Unique Key:

A unique key is a constraint that does not contain any duplicate value.
It may contain a null constraint.
We can define more than one column as a unique key.

create table mytable(
id int not null primary key,
name varchar(50) not null,
adhar int unique
);

So, my dear readers, that’s all for today. I tried my best to differentiate all major things b/w primary key, foreign key, and unique key. I will keep writing about these types of confusing topics. So, keep in touch with my articles. Thank You…