Difference Between Primary Key and Foreign Key

By Mona Kumari|Updated : April 25th, 2022

Difference between primary key and foreign key: In a database, a key plays a vital role, especially in RDBMS. The DBMS consists of a number of distinct keys such as the candidate key, super key, primary key, and foreign key. In this article, we will learn about the difference between primary key and foreign key.

Key in the database ensures uniqueness of data and also has other advantages which help in managing data efficiently. While discussing the difference between primary key and foreign key, we will learn about other keys present in RDBMS. To ensure the uniqueness of data in relation we have to have at least one primary key present, a foreign key may or may not be present. There are several difference between primary key and foreign key but before discussing the difference between the two, let us discuss each one in detail.

Table of Content

What is a Key?

A key in DBMS or RDBMS is a constraint that ensures the uniqueness of data in the relation or table. RDBMS consists of various keys like candidate key, primary key, super key and foreign key. For a relation in RDBMS, it is necessary to have at least one candidate or primary key.

What is a Primary Key?

A primary key is one of the candidate keys. Now before understanding the difference between primary key and foreign key, we have to understand the candidate key and super key first and foremost. A candidate key is the minimal set of attributes that can uniquely identify a row in a relation.

A candidate key is a subset of a super key. A super key is one that can identify a relation uniquely and which may not be the minimal set. Super key acts as a superset for the candidate key. All the candidate keys are also super keys, but vice versa is not true. A primary key is one of the candidate keys that is chosen by the administrator to identify row/ tuples uniquely.

What is a Foreign Key?

A foreign key is used to refer to one or more relations in RDBMS. One of the major concerns of having a foreign key is data integrity between two different relations. A foreign key refers to the primary key of the same relation or of a different relational instance.

Difference between Primary Key and Foreign Key

We are done with the prerequisites terminologies to understand the difference between primary key and foreign key. Now let’s see the differences between the two listed in the table below.

Primary Key

Foreign Key

A relation can have only one primary key.

A relation can have more than one foreign key.

A primary key value cannot be null.

A foreign key allows null values.

A primary key is a combination of unique and not null constraints.

A foreign key can contain duplicate values.

A primary key value cannot be deleted from the parent table.

A foreign key can be deleted from the child's table.

Its constraints can be implicitly defined on the temporary tables.

Its constraint cannot be defined on the local and global temporary tables.

It uniquely identifies a record in the relational database table.

It refers to the field in a table that is the primary key of the same or another table.

BYJU's Exam Prep Online Classroom Program 

Thanks

#DreamStriveSucceed

Comments

write a comment

FAQs

  • The basic difference between primary key and foreign key is such that the primary key is used to ensure that each column's value is unique whereas, the foreign key is what connects the two tables together.

  • Null values are defined as unknown or un-existed values. A primary key cannot contain a null value, while a foreign key can contain a null value.

  • A primary is mandatory in an RDBMS relation. Every relation should have at least and at most one primary key, while a foreign key can be present more than once in a relation.

  • A primary key is designed to ensure uniqueness in the relational database. Foreign keys do not ensure uniqueness but are used to link data between different relations.

  • A relation in RDBMS can have one or more candidate keys. A primary key is one of the candidate keys. Candidate keys are a subset of the super key with a minimal set of attributes that can distinguish tuples uniquely.

  • A candidate key is defined as the minimal set of attributes that can uniquely identify a tuple in a relation. A candidate key is the subset of the super key.

Follow us for latest updates