Primary Key vs Foreign Key: What's the Difference?

Details

Before learning the difference between primary key and foreign key, let's learn:

What are Keys?

Keys are attribute that helps you to identify a row(tuple) in a relation(table). They allow you to find the relationship between two tables. Keys help you uniquely identify a row in a table by a combination of one or more columns in that table. The database key is also helpful for finding a unique record or row from the table.

What is Database Relationship?

The database relationship is associations between one or more tables that are created using join statements. It is used to efficiently retrieve data from the database. There are primarily three types of relationships 1) One-to-One, 2) One-to-many, 3) Many-to-many.

What is Primary Key?

A primary key constrain is a column or group of columns that uniquely identifies 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 have more than one primary key. Primary key can be defined at the column or the table level. If you create a composite primary key, it should be defined at the table level.

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.

KEY DIFFERENCES:

  • A primary key constrain is a column that uniquely identifies every row in the table of the relational database management system, while foreign key is a column that creates a relationship between two tables.
  • Primary Key never accepts null values whereas 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 foreign key value 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 in inserting the values into the table column while inserting any value in the foreign key table, ensure that the value is present into a column of a primary key.

Why use Primary Key?

Here are the cons/benefits of using primary key:

Why use Foreign Key?

Here are the important reasons of using foreign key:

Example of Primary Key

Syntax:

Below is the syntax of Primary Key: 

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

Here,

Example:

StudID Roll No First Name Last Name Email
1 11 Tom Price This email address is being protected from spambots. You need JavaScript enabled to view it.
2 12 Nick Wright This email address is being protected from spambots. You need JavaScript enabled to view it.
3 13 Dana Natan This email address is being protected from spambots. You need JavaScript enabled to view it.

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.

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,

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, 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.

Difference between Primary key and Foreign key

Here is the important difference between Primary key and Foreign key:

Primary Key Foreign Key
A primary key constrain is a column or group of columns that uniquely identifies every row in the table of the relational database management system. Foreign key is a column that creates a relationship between two tables.
It helps you to uniquely identify a record in the table. It is a field in the table that is a primary key of another table.
Primary Key never accepts null values. A foreign key may accept multiple null values.
The primary key is a clustered index, and data in the DBMS table are physically organized in the sequence of the clustered index. A foreign key cannot automatically create an index, clustered, or non-clustered.
You can have the single Primary key in a table. You can have multiple foreign keys in a table.
The value of the primary key can't be removed from the parent table. The value of foreign key value can be removed from the child table.
You can define the primary key implicitly on the temporary tables. You cannot define foreign keys on the local or global temporary tables.
Primary key is a clustered index. By default, it is not a clustered index.
No two rows can have any identical values for a primary key. A foreign key can contain duplicate values.
There is no limitation in inserting the values into the table column. While inserting any value in the foreign key table, ensure that the value is present into a column of a primary key.

 

YOU MIGHT LIKE: