Difference Between Primary Key and Foreign Key | Primary vs Foreign Key

By Mona Kumari|Updated : October 12th, 2022

Difference Between Primary Key and Foreign Key: A key plays a vital role in a database, especially in RDBMS. The DBMS consists of several distinct keys, such as the candidate, super, primary, and foreign keys. Key in the database ensures the uniqueness of data and has other advantages that help manage data efficiently.

While discussing the difference between primary and foreign keys, we will also learn about other keys in RDBMS. To ensure the uniqueness of data in relation, we must have at least one primary key; a foreign key may or may not be present. There are many differences between primary and foreign keys, which will be discussed here in detail.

Table of Content

Difference between Primary Key and Foreign Key

The major difference between primary key and foreign key is that the primary key is used to identify each entry in the table, whereas the foreign key is used to connect two tables.

Differentiate Between Primary Key and Foreign Key

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.

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. 

SQL Server has two types of keys: main and foreign. While they appear to be the same, they differ in functionality and behaviors. 

Difference between Primary Key and Foreign Key with Example

Let us now understand the difference between foreign key and Primary Key with an example. Check out the tables provided in the image shown below:

Difference between Primary Key and Foreign Key with Example

In table 1(STUDENT), the key STUD_NO is the primary key, whereas the STUD_NAME, STUD_COUNT, and STUD_AG are candidate keys. Now, STUD_NO in table 2(STUDENT_COURSE), STUD_NO will be the foreign key for table 1 relation(STUDENT).

Difference between Primary Key and Foreign Key PDF

What is a Primary Key?

A primary key is one of the candidate keys. Now after understanding the difference between the primary key and foreign key, we have to understand the candidate key and super key. A candidate key is the minimal set of attributes that can uniquely identify a row in a relation. The uniqueness of the table records is usually the focus of a primary key. And each row in the database is uniquely identified by a column or a collection of columns. It signifies that there should be no duplicate values for the same column across the table.

A candidate key is a subset of a super key. A super key can identify a relation uniquely and 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 chosen by the administrator to identify row/ tuples uniquely.

What is a Foreign Key?

A foreign key refers 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 is the primary key of the same or a different relational instance.

The foreign key is usually used to establish a link or relationship between the two tables. The foreign key's principal purpose is to maintain data integrity between two independent instances of an entity simultaneously.

Further, let us see some articles related to the Difference between Primary Key and Foreign Key:

Comments

write a comment

FAQs on Difference Between Primary Key and Foreign Key

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

  • The difference between foreign key and primary key in terms of the number of times they can be present in relations that a primary is mandatory in an RDBMS relation. Every relation should have at least 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