Difference Between Primary Key and Foreign Key

By Mona Kumari|Updated : May 9th, 2023

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.

Difference between Primary Key and Foreign Key PDF

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.

Primary Key vs 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).

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.

Importance of Primary and Foreign Keys in Database Design

The primary and foreign keys are essential elements of database design, and their importance cannot be overstated. They ensure data integrity, improve query performance, allow for efficient data relationships, and facilitate database maintenance.

Here's a tabular and precise description of the importance of primary and foreign keys in database design:

ImportanceDescription
Data IntegrityPrimary keys ensure that each record in a table is unique, while foreign keys maintain referential integrity between related tables. This helps ensure that the data in the database is accurate and consistent.
Query PerformancePrimary and foreign keys are used to create indexes, which can speed up query performance. Indexes allow the database to quickly find and retrieve the data needed to satisfy a query. This can improve the performance of complex queries, particularly when working with large amounts of data.
Data RelationshipsForeign keys establish relationships between tables, allowing data to be efficiently linked and retrieved from multiple tables in a database. This is particularly useful for complex data structures where data is spread across multiple tables. It enables efficient data retrieval, avoids data duplication, and provides a way to navigate through related data, enabling the creation of complex reports and queries.
Database MaintenancePrimary and foreign keys are critical for maintaining the integrity of a database. Without these keys, it would be challenging to modify or delete records in a table without affecting the data in other tables. These keys provide a means of maintaining data consistency and avoiding data loss, which is essential for ensuring data reliability and the successful operation of the database.

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