Difference Between Primary Key and Foreign Key

Key Differences between Primary Key vs Foreign Key

A primary key constraint is a column that uniquely identifies every row in the table of the relational database management system, while a foreign key is a column that creates a relationship between two tables.
The primary key never accepts null values, whereas the foreign key may accept multiple null values.
You can have only a single primary key in a table, while you can have multiple foreign keys in a table.
The value of the primary key can’t be removed from the parent table, whereas the value of the foreign key can be removed from the child table.
No two rows can have any identical values for a primary key; on the other hand, a foreign key can contain duplicate values.
There is no limitation to inserting the values into the table column while inserting any value in the foreign key table; ensure that the value is present in a column of a primary key.

Difference Between Primary Key and Foreign Key

Here, I have analyzed the difference between the primary key and the foreign key and will comprehensively evaluate their pros and cons.

What is Primary Key?

A primary key constraint is a column or group of columns that uniquely identify every row in the table of the relational database management system. It cannot be a duplicate, meaning the same value should not appear more than once in the table.

A table can’t have more than one primary key. The primary key can be defined at the column or table level. If you create a composite primary key, it should be defined at the table level.

Example of a Primary Key

Syntax:

Below is the syntax of Primary Key:

CREATE TABLE <Table-Name>
(
Column1 datatype,
Column2 datatype,  PRIMARY KEY (Column-Name)
.
);

Here,

Table_Name is the name of the table you have to create.
Column_Name is the name of the column having the primary key.

Example:

StudID	Roll No	First Name	Last Name	Email
1	11	Tom	Price	abc@gmail.com
2	12	Nick	Wright	xyz@gmail.com
3	13	Dana	Natan	mno@yahoo.com

In the above example, we have created a student table with columns like StudID, Roll No., First Name, Last Name, and Email. StudID is chosen as a primary key because it can uniquely identify other rows in the table.

Why use Primary Key?

Throughout my years of handling database systems, I’ve noted these cons and benefits when using a primary key:

The main aim of the primary key is to identify each and every record in the database table.
You can use a primary key when you do not allow someone to enter null values.
If you delete or update records, the action you specified will be undertaken to ensure data integrity.
Perform the restrict operation to reject the delete or update operation for the parent table.
Data are organized in a sequence of clustered indexes whenever you physically organize a DBMS table.

What is Foreign Key?

Foreign key is a column that creates a relationship between two tables. The purpose of the Foreign key is to maintain data integrity and allow navigation between two different instances of an entity. It acts as a cross-reference between two tables as it references the primary key of another table. Every relationship in the database should be supported by a foreign key.

Example of Foreign Key

Syntax:

Below is the syntax of Foreign Key:

CREATE TABLE <Table Name>( 
column1    datatype,
column2    datatype,  
constraint (name of constraint) 
FOREIGN KEY [column1, column2...] 
REFERENCES [primary key table name] (List of primary key table column) ...);

Here,

The parameter Table Name indicates the name of the table that you are going to create.
The parameters column1, column2… depicts the columns that need to be added to the table.
A constraint denotes the name of the constraint you are creating.
References indicate a table with the primary key.

Example:

DeptCode	DeptName
001	Science
002	English
005	Computer

Teacher ID	Fname	Lname
B002	David	Warner
B017	Sara	Joseph
B009	Mike	Brunton

In the above example, we have two tables: a teacher and a department in a school. However, there is no way to see which search works in which department.

In this table, by adding the foreign key in Deptcode to the teacher name, we can create a relationship between the two tables.

Teacher ID	DeptCode	Fname	Lname
B002	002	David	Warner
B017	002	Sara	Joseph
B009	001	Mike	Brunton

This concept is also known as referential integrity.

Why use Foreign Key?

From what I’ve seen, here are the important reasons to implement foreign keys:

Foreign keys help you to migrate entities using a primary key from the parent table.
A foreign key enables you to link two or more tables together.
It makes your database data consistent.
A foreign key can be used to match a column or combination of columns with the primary key in a parent table.
SQL foreign key constraint is used to make sure the referential integrity of the data parent matches values in the child table.