Primary Key vs Foreign Key: Difference Between Primary and Foreign Key

In relational databases, keys play a vital role in ensuring data integrity and establishing relationships between tables. A primary key uniquely identifies each record within a table, while a foreign key creates a link between two tables, referencing the primary key of another table.  

This tutorial will guide you through the key differences between primary and foreign keys, their functions, constraints, and practical examples. By the end of this guide, you will clearly understand how to use these keys effectively for efficient database design and relational data management.  

Want to strengthen your software development skills? Explore upGrad’s Online Software Engineering Courses. Build a strong foundation in JavaScript, Node.js, APIs, React, and more to accelerate your career in software engineering.

What are Keys in SQL?

In SQL, keys are used to uniquely identify records within a table. They ensure data integrity and enable efficient data retrieval. Depending on the need, keys can be composite or single-field. To further comprehend this, let's look at an illustration.

Accelerate your tech career by mastering future-ready skills in Cloud, DevOps, AI, and Full Stack Development. Gain hands-on experience, learn from industry leaders, and develop the expertise that top employers demand. 

• Professional Certificate Program in Cloud Computing and DevOps

• AI-Powered Full Stack Development Course by IIITB

• Future-Proof Your Tech Career with AI-Driven Full-Stack Development

Let's say we have the following fields in a table called "Employees":

EmployeeID (Primary Key)

FirstName

LastName

DepartmentID (Foreign Key)

Here, the EmployeeID field serves as the primary key, uniquely identifying each employee. The DepartmentID field acts as a foreign key, establishing a relationship between the "Employees" table and the "Departments" table. The primary and foreign keys work together to maintain data integrity and consistency.

What is a Primary Key?

A primary key is a field or combination of fields in a table that uniquely identifies each record. It ensures that there are no duplicate records and guarantees data integrity. Typically, a primary key is chosen from the available candidate keys in a table. Here's the difference between primary key and foreign key example:

Consider a table called "Students" with the following fields:

StudentID (Primary Key)

FirstName

LastName

Age

The StudentID field, in this instance, acts as the primary key, guaranteeing that every student has a distinct identification. The StudentID is unique to each student, preventing data duplication and facilitating quick access to student-specific data.

What is the Use of Primary Key?

The primary key serves multiple purposes in a database:

• Uniquely identifies records: The primary key ensures that each record in a table has a unique identifier. This uniqueness helps in distinguishing individual records from one another.
• Ensures data integrity: By preventing duplicate records, the primary key ensures data integrity within a table. It guarantees that no two records have identical primary key values.
• Facilitates data retrieval: The primary key allows for the efficient retrieval of specific records. It serves as a reference point for searching and locating data.

To illustrate the use of a primary key, let's consider the "Students" table mentioned earlier. Suppose we want to retrieve information about a specific student with StudentID 1234. The primary key enables us to quickly locate and retrieve the relevant record.

Also Read: Software Developer vs Software Engineer: Key Differences, Similarities and More

What is a Foreign Key?

A foreign key is a field or combination of fields in a table that establishes a link or relationship between tables. It refers to the primary key of another table, creating a connection between them. Foreign keys enable the implementation of referential integrity and maintain data consistency across related tables. Let's delve into an example:

Continuing with the previous example, we have a table called "Departments" with the following fields:

DepartmentID (Primary Key)

DepartmentName

Let's now use a foreign key to link the "Employees" and "Departments" tables together. The DepartmentID field in the "Employees" database functions as a foreign key that refers to the primary key in the "Departments" table.

What is the Use of Foreign Key?

The foreign key serves several purposes in a database:

• Establishes relationships between tables: The foreign key establishes a link between tables, enabling the creation of relationships. It connects data across different tables, facilitating data consistency and integrity.
• Enforces referential integrity: By referencing the primary key of another table, the foreign key ensures that the linked values exist. It prevents orphaned records by enforcing referential integrity.
• Supports data integrity: The foreign key helps maintain data integrity by ensuring that data dependencies are preserved. It prevents inconsistent or invalid data from being inserted into related tables.

To illustrate the use of a foreign key, consider the "Employees" and "Departments" tables. The DepartmentID field in the "Employees" table acts as a foreign key, referencing the primary key (DepartmentID) of the "Departments" table. This connection allows us to establish relationships between employees and their respective departments.

Must Read: Difference Between Linear and Non-Linear Data Structures

Difference between Primary Key and Foreign Key

Although both primary keys and foreign keys are key components in database design, they serve different purposes and exhibit distinct characteristics. Here is the key difference between primary key and foreign key definition:

Definition and Purpose:

• Primary Key: A primary key is a unique identifier for each record within a table. It ensures data integrity and enables efficient data retrieval.
• Foreign Key: A foreign key establishes a relationship between two tables by referencing the primary key of another table. It maintains referential integrity and supports data consistency across tables.

Uniqueness:

• Primary Key: The primary key must be unique within a table. It ensures that each record has a unique identifier, preventing data duplication.
• Foreign Key: The foreign key references the primary key of another table, and it may contain duplicate values within the table. It represents the link between tables rather than guaranteeing uniqueness within the table.

Data Modification:

• Primary Key: The primary key values are typically immutable, as they serve as permanent identifiers for records. Changing the primary key value is discouraged due to potential data integrity issues.
• Foreign Key: The foreign key values can be modified, allowing for changes in the relationships between tables. Updating the foreign key value in one table can modify the associated record in the referenced table.

Table Association:

• Primary Key: The primary key is associated with the table where it is defined. It uniquely identifies records within that table.
• Foreign Key: The foreign key is associated with a different table than where it is defined. It establishes a relationship with the referenced table, connecting data across tables.

Dependency:

• Primary Key: The primary key is independent and does not rely on other tables or their keys. It uniquely identifies records within a table.
• Foreign Key: The foreign key relies on the referenced table and its primary key. It establishes a dependency on the primary key values of another table.

Constraints:

• Primary Key: The primary key enforces the uniqueness and non-nullability of its values within a table. It is often defined as the table's primary index.
• Foreign Key: The foreign key ensures that the referenced values exist in the referenced table's primary key. It may also specify cascading actions for data modification or deletion.

Also Read: What Are The Types of Keys in DBMS? Examples, Usage, and Benefits

Primary Key vs Foreign Key Comparison Chart 

To summarize the differences between primary keys and foreign keys, refer to the following chart: 

Also Read: Primary Key in SQL Database: What is, Advantages & How to Choose 
Must Read: Primary Key In SQL: A Complete Guide 

Conclusion 

In summary, understanding the difference between primary key and foreign key is essential for effective database design. A primary key uniquely identifies each record in a table, ensuring data integrity and enabling fast data retrieval. A foreign key links tables by referencing the primary key of another table, maintaining referential integrity and consistent relationships.  

Knowing how and when to use primary and foreign keys helps database designers build robust, efficient, and well-structured databases. By mastering these concepts, you can ensure data accuracy, enforce relationships, and optimize relational database performance. 

FREE COURSES

Start Learning For Free

Slide 1 of 5

Object-Oriented Principles in Java

13k+ learners

12 hrs of learning

View Program

Enroll Now

Data Structures & Algorithm

35k+ learners

50 hrs of learning

View Program

Enroll Now

Core Java Basics

14k+ learners

23 hrs of learning

View Program

Enroll Now

Case Study using Tableau, Python and SQL

11k+ learners

10 hrs of learning

View Program

Enroll Now

Learn Basic Python Programming

43k+ learners

5 hrs of learning

View Program

Enroll Now

Explore All Free Courses