Difference Between Primary Key and Foreign Key in SQL

Each programmer should be efficient at working with data. Therefore, one of the most essential abilities needed to become a programmer is proficiency in understanding the difference between Primary Key and Foreign Key in SQL. keys are the structural foundation of any relational database. The Difference Between Primary key and Foreign key in SQL is that a Primary key is an identifier that is specific to each record in a table. By referring to the Primary Key of another table, a Foreign Key creates a connection between two tables. This is just scratching the surface, these keys can further be differentiated in many ways. In this blog, we will talk about why we use Primary and Foreign Keys in SQL, their benefits followed by the critical difference between Primary Keys and Foreign Keys in SQL. 

Table of contents

1) Let’s understand what a key is 

    a) What is a Primary Key? 

    b) What is a Foreign Key? 

2) Understanding the benefits of Primary Key and Foreign Key 

3) Critical differences between Primary Key and Foreign Key in SQL  

4) Why use Primary Key? 

5) Why use Foreign Key? 

6) Example of Primary Key 

7) Example of Foreign Key 

8) Conclusion  

Let’s understand what a key is

Keys are a property that make it easier to identify a row in a relation table. They enable you to discover the connection between two tables. By combining one or more than one table in a column, keys allow you to identify each row in the table uniquely. The database key can make finding a particular record or row from the table easier.   

A key in Database Management System (DBMS) or Relational Database Management System (RDBMS) ensures the unique data creation in the relation or table. Keys are the fundamental elements for building a relationship between two tables. A Structured Query language (SQL) has two keys: Primary and Foreign Key in SQL. Let's have a detailed look at each of them:   

What is a Primary Key?  

In a Relational Database Management System, a Primary Key constraint is a column or set of columns uniquely identifying each row in the table. It is employed to ensure that data in a particular column is unique.   

The following are the benefits of Primary Key:  

a) Prevents entering null values   

b) Data integrity   

c) Data is organised in a sequence   

What is a Foreign Key?   

A database's Foreign Key is a collection of columns used to uniquely identify a database record in a different table and maintain referential integrity. A Foreign Key is helpful when structuring a relational database, especially when accessing data from other tables.     

The following are the benefits of Foreign Key:  

a) Allows you to link two or more tables together   

b) It ensures data consistency in your database   

c) You can move items using a Foreign Key in a parent table   

Aspire to master MySQL? Sign up for our Introduction to MySQL Course  

Understanding the benefits of – Primary Key and Foreign Key 

Primary and Foreign Keys in SQL contribute to the structure of a relational database, helping users understand the keys' functionality. A Primary Key ensures that each row is identified uniquely. It speeds up sorting, searching, and querying activities. A Foreign Key connects two tables. It maintains referential integrity (logical dependency of a Foreign Key on a Primary Key) between the referencing and referenced columns.

Let us now see the benefits of using Primary Key and Foreign Key. They are as follows: 

1) Primary Key: 

a) Uniqueness: The Primary Key ensures that every row in a table can be uniquely identified. No two rows can have the same Primary Key value. 

b) Data integrity: By enforcing uniqueness, the Primary Key ensures the integrity and consistency of the data in the table. 

c) Performance: Indexes are automatically created for Primary Keys, leading to faster retrieval of data. These indexes allow for more efficient queries and lookups. 

d) Relationships: The Primary Key is essential for creating relationships with other tables. It acts as a reference point for Foreign Keys in other tables. 

e) Simplicity: Often, Primary Keys simplify how you can reference a particular record, especially if they're implemented as simple auto-incremented integers. 

2) Foreign Key: 

a) Referential integrity: Foreign Keys ensure that the relationships between tables are maintained. They ensure that records in one table correspond to records in another table. For instance, if there's a studentID in a Grades table, a Foreign Key can ensure that this studentID exists in the student's table. 

b) Prevents invalid data: With Foreign Key constraints, you cannot enter a value in the Foreign Key column if that value does not exist in the referenced Primary Key column. This prevents accidental insertion of incorrect or nonsensical data. 

c) Cascade actions: Foreign Keys can be set up to perform certain actions when data is updated or deleted. For example, if a record in the primary table is deleted, the corresponding records in the Foreign Key table can be automatically deleted (CASCADE DELETE) or updated. 

d) Logical relationships: The use of Foreign Keys make the relational aspect of databases clear, helping database designers, developers, and administrators understand the connections between different tables. 

e) Query enhancement: Having proper Foreign Key relationships allow for more complex and relational queries, which can join multiple tables together based on these relationships to fetch or manipulate data. 

Join our SQL courses to unlock the full potential of databases. With hands-on learning and industry-focused training,

Critical differences between Primary Key and Foreign Key in SQL

Let’s take a look at the crucial differences between Primary Key and Foreign Key in SQL: 
 

Primary Key	Foreign Key
A Primary Key constraint in a relational database management system is a set of columns or a column that identifies each row in the table uniquely.	A Foreign Key is a column that connects two tables.
It allows you to identify a record in the table uniquely.	It serves as the main key for another table.
It does not accept null values	It accepts multiple null values
You can have one Primary Key in a table	You can have many Foreign Keys in a table
The value of the Primary Key cannot be removed from the parent table.	The value of the Foreign Key can be removed from the child table
It is a clustered index	It is not a clustered index
You can define the Primary Key automatically on the temporary tables.	You cannot define the Foreign Key in temporary tables.
A Primary Key value cannot be the same in two rows.	.A Foreign Key can have duplicate values.
The values can be entered into the table column without restriction.	When entering a value into the Foreign Key table, make sure it is present in a column of a Primary Key.

 

Master SQL with our course on Introduction To SQL 

Why use Primary Key? 

There are several reasons why you should use Primary Key. They are as follows: 

a) Efficient data retrieval: These keys are typically indexed by default. Indexing speeds up the data retrieval process, which allows the Database Management System (DBMS) to locate and access the row data more rapidly, that it would take to index. 

b) Systematic storage: Records are stored in an organised manner, especially when the Primary key is an auto-incremented integer. 

c) Simplifies data management: Primary Keys simplify tasks like updating records, as there’s an assurance that only one record will be affected due to the unique identifier. 

d) Data consistency: They reduce the risk of random data linkage or misinterpretation of data. This leads to more consistent and reliable data handling. 

e) Enhances data security: Some systems require row-level security. Having Primary keys help in efficiently identifying which data can be accessed or modified.

Dive deeper into database – Register now for our Advanced SQL Training 

Why use a Foreign Key? 

Here are some reasons which will tell you why you should use a Foreign Key: 

a) Establish relationships: Using Foreign Keys helps to define relationships between tables. This relationship ensures that records in one table have corresponding records in another. 

b) Data consistency: Foreign Keys prevent inconsistent data. If a table references another through a Foreign Key, it is impossible to add records with non-existent reference values. 

c) Data retrieval: Using Foreign keys, it is easier to retrieve data from multiple tables. This makes it more intuitive and efficient. 

d) Database optimisation: Foreign keys help database engine optimise query performance by leveraging the existing relationships.

To have a better understanding on both the keys lets dive into few examples below with basic structure of SQL queries demonstrating Primary Key and Foreign Key.

Example of Primary Key 

Let's consider a simple example: a ‘Students’ table that stores information about students in a school. 

Here's how to usePrimary Key the ‘Students’ table: 

CREATE TABLE Students ( 

StudentID INT NOT NULL PRIMARY KEY, 

FirstName VARCHAR(50), 

LastName VARCHAR(50), 

DateOfBirth DATE, 

Address VARCHAR(100) 

); 

The following can be derived after using the above formulas:  

a) ‘StudentID’ is the Primary Key. It's an integer value that is unique for each student. No two students can have the same ‘StudentID’. 

b) Every time a new student is added, they must be given a unique ‘StudentID’. 

c) The ‘PRIMARY KEY’ constraint ensures that there are no duplicate values in the ‘StudentID’ column and that no value is null. 

Let's say you add two students: 

INSERT INTO Students (StudentID, FirstName, LastName, DateOfBirth, Address) 

VALUES (1, 'John', 'Doe', '2000-05-15', '123 Elm Street'); 

INSERT INTO Students (StudentID, FirstName, LastName, DateOfBirth, Address) 

VALUES (2, 'Jane', 'Smith', '2001-06-20', '456 Oak Avenue'); 

In this example, John Doe has a ‘StudentID’ of 1 and Jane Smith has a ‘StudentID’ of 2. These IDs are unique and will be used to identify each student in the database. If you tried to insert another student with an ID of 1 or 2, the database would reject it because the Primary Key constraint ensures the uniqueness of the ‘StudentID’ column.
 

Example of Foreign Key 

Suppose we want to record the subjects that each student has registered for . For this, we can have another table, ‘Enrollments’. Each enrolment will link a student to a subject. For our purposes, let's also assume we have a ‘Subjects’ table that lists all available subjects. 

Here are the tables: 

1) Students table (as defined before): 

CREATE TABLE Students ( 

StudentID INT NOT NULL PRIMARY KEY, 

FirstName VARCHAR(50), 

LastName VARCHAR(50), 

DateOfBirth DATE, 

Address VARCHAR(100) 

); 

2) Subjects table: 

CREATE TABLE Subjects ( 

SubjectID INT NOT NULL PRIMARY KEY, 

SubjectName VARCHAR(100) 

); 

3) Enrolments table: 

 Each enrolment relates a student to a subject. The relationship is represented by using the IDs from the Students and Subjects tables. Here how we'll use a Foreign Key: 

CREATE TABLE Enrollments ( 

EnrollmentID INT NOT NULL PRIMARY KEY, 

StudentID INT, 

SubjectID INT, 

EnrollmentDate DATE, 

FOREIGN KEY (StudentID) REFERENCES Students(StudentID), 

FOREIGN KEY (SubjectID) REFERENCES Subjects(SubjectID) 

); 

The following can be derived after using the above formulas Iin the Enrollments table: 

a) ‘StudentID’ and ‘SubjectID’ are foreign keys. 

b) The ‘FOREIGN KEY’ constraint for ‘StudentID’ ensures that any value entered into this column corresponds to a valid ‘StudentID’ in the ‘Students’ table. 

c) Similarly, the ‘FOREIGN KEY’ constraint for ‘SubjectID’ ensures that any value entered corresponds to a valid ‘SubjectID’ in the Subjects table. 

d) This setup ensures that we can't accidentally enrol a non-existent student in a subject or enrol a student in a non-existent subject, preserving data integrity. 

Example of enrolling a student into a subject: 

-- Assuming Student with StudentID = 1 exists and Subject with SubjectID = 101 exists 

INSERT INTO Enrollments (EnrollmentID, StudentID, SubjectID, EnrollmentDate) 

VALUES (1, 1, 101, '2023-09-08'); 

This entry indicates that the student with StudentID 1 has enrolled in the subject with SubjectID 101 on September 8, 2023. 

Transform your data insights with SQL Server Reporting Services (SSRS) Masterclass 

Conclusion 

We hope that after reading this blog you have understood the difference between Primary Key and Foreign Key, the examples of using them and their benefits. 

Empower your database management skills with PostgreSQL Administration Training .