Difference Between Primary Key And Foreign Key Explained (Example)

Keys play a crucial role in organizing and maintaining the integrity of data in relational database management. Keys are special columns in a table that are used to identify records uniquely and establish relationships between different tables. In this article, we will explore the difference between primary key and foreign key, two important types.

A primary key is a unique identifier for a record in a table. It ensures that each record can be uniquely identified and retrieved. A foreign key, on the other hand, is a column or set of columns in one table that uniquely identifies a row of another table or the same table. It is used to establish and enforce a link between the data in the two tables.

What Are Keys?

As you must know, a database refers to the collection of data where the records may be organized in the form of a table. In relational database management systems, keys are essential elements used to define relationships, maintain data integrity, and ensure efficient data retrieval.

A key is a specific field or combination of fields in a table that is used to identify, manage, and retrieve records.
A key in tables also helps establish a relationship between two or more tables.
In other words, keys are fundamental to the structure and functionality of relational databases.
There are six types of keys: primary key, foreign key, candidate key, super key, alternate key, and composite key.

In this article, we will explore the difference between primary key and foreign key in detail, along with the individual concepts.

Difference Between Primary Key And Foreign Key

Below is a table highlighting the major differences between the primary key and foreign keys.

Aspect	Primary Key	Foreign Key
Uniqueness	It uniquely identifies each row/ record in a table, and no two relational database table rows can have identical values.	It is a field in one table that establishes the relation between tables. In other words, it is a column that uniquely identifies a row of another table or the same table.
NULL Values	It cannot contain a NULL value.	It can contain NULL values unless explicitly defined as NOT NULL.
Quantity in a Table	A table can have only one primary key.	The table can have multiple foreign keys.
Uniqueness/ Enforcement	It uniquely identifies values in the relational database table.	It references the primary key or a unique key in another relational database table, enforcing referential integrity constraints.
Constraints	A primary key is a combination of UNIQUE and NOT NULL constraints. Hence, there are no duplicate values in the primary key field.	A foreign key does not inherently have UNIQUE or NOT NULL constraints. It can contain duplicate and NULL values.
Implicit Definition	On the temporary table and associated variables, a primary key constraint can be defined implicitly.	A foreign key constraint cannot be defined implicitly on a local or global temporary table.
Deletion of Key Values	A primary key value in the parent table, which is referred to as a foreign key in the child table, cannot be deleted if it is referenced in a child table.	A foreign key value in the child table might be deleted, but rules of referential integrity between tables must be considered.
Data Insertion	It is possible to enter values into primary keys without considering foreign keys.	If the values aren't in the primary key, they can't be inserted into foreign keys.
Parent-Child Relationship	A primary key defines a unique identity for the table records, not necessarily the parent-child connection.	A foreign key creates a parent-child relationship between tables.
Indexing	Primary keys, by default, create a clustered index (unless specified otherwise).	Foreign keys do not automatically create an index, but it is often beneficial to manually create an index for performance.
Purpose	Ensures each record in a table can be uniquely identified.	Establishes and enforces a link between the data in two tables.
Role in Joins	Used in JOIN operations to uniquely identify and retrieve records from the table.	Used in JOIN operations to reference records in another table, establishing a relational link.
Example Usage	CREATE TABLE Customers (CustomerID INT PRIMARY KEY, Name VARCHAR(50));	CREATE TABLE Orders (OrderID INT PRIMARY KEY, CustomerID INT, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID));

Now that we know about the key difference between primary key and foreign key, let's explore the individual concepts with the help of examples.

What Is A Primary Key?

A primary key is a table column or a set of columns in a database table that uniquely identifies each row in that table. It is a fundamental aspect of a relational database's structure because it ensures that each record is distinct and can be referenced unambiguously.

The primary key attests to the uniqueness of an entry in a table column or row.
The primary key column cannot contain duplicate values. In other words, primary key columns contain values that uniquely identify each row in a table.
The values in these columns can also be referred to as parent column values.

Some common examples of primary keys are - Driver's license number, Passport number, and Voter ID. These are unique to a person and cannot be associated with more than one individual in the database record.

Features Of Primary Key

Here are a few main features of the primary key in relational database schema:

Uniqueness: Every value in the primary key column(s) must be unique across the table, ensuring that no two rows can have the same primary key value.
Non-nullability: Primary key columns cannot contain NULL values. Every row must have a valid value for the primary key.
Indexing: Primary keys are often indexed by the database system to improve the speed of data retrieval operations.
Single or Composite: A primary key can be a single column or a combination of multiple columns (composite key).

Purpose Of Primary Key

The primary key column in any table is used to identify the records of each row. Since it can not contain duplicate values, it is easy to distinguish among the records, even if they are similar in other columns. Sometimes, a single-row primary key is not enough to identify the records. Hence, a group of columns is selected as a primary key, known as a composite primary key.

Syntax Of Primary Key

In the syntax below, NOT NULL and PRIMARY KEY are key constraints that are applied to the described columns.

Syntax (Oracle):

Create table table_name(
column_name1 datatype NOT NULL PRIMARY KEY,
column_name2 datatype NOT NULL,
column_name3 datatype
);

Example Of Primary Key

Say your college maintains all the student records in the form of a table stored in DBMS. This database table might need to be edited in the future to add, update or delete the entries/ information. DBMS uses key attributes associated with the columns to handle all these operations. Look at the table below:

Student ID	Student Name	Student Branch	Student Email ID
100	Riya	CSE	riya@gmail.com
101	Aman	CSE	aman@yahoo.in
102	Deepak	Mech	deepak@yahoo.com
103	Barkha	IT	barkha@gmail.com

In this, Student ID is unique for every student and is hence considered as a primary key for the table. A database must have a primary key column to uniquely insert, update, restore, or delete data from a database table.

What Is A Foreign Key?

A foreign key is generally used to establish the relationships between tables in relational database management systems (RDBMS).

It is a column or a set of columns in a database table that establishes a link between the data in two tables. In other words, it is used to navigate between two tables and maintain referential integrity.
The primary key of one table acts as a foreign key for another and helps us to cross-reference two or more tables.

In short, the foreign key in one table points to the primary key in another table, thereby creating a relationship between the tables.

Features Of Foreign Key

Here are a few main features of the foreign key in relational database schema:

Referential Integrity: Foreign keys enforce referential integrity by ensuring that a value in the foreign key column must exist in the primary key column of the referenced table.
Data Consistency: They help maintain consistency across related tables by ensuring that relationships between records are valid.
Cascading Actions: Foreign keys can define actions (like ON DELETE CASCADE) that automatically update or delete dependent rows when the referenced row is updated or deleted.

Purpose Of Foreign Keys

There are two reasons for using foreign keys:

Maintains the data integrity: A foreign key in a database is used to maintain the data integrity among the tables. Let's suppose you delete the record of one person from one table and forget to delete it from the other tables. This would lead to data inconsistency, and tons of blank records would be present in the other table. But with the help of foreign key constraints, if you delete any record from a table, it is automatically deleted from all other tables.
Faster retrieval of records: Since a foreign key is used to link two or more tables, the retrieval of records becomes easier and more accurate.

Syntax Of Foreign Key

Unlike the syntax for the primary key, here, we do not have the NOT NULL constraint. Also, REFERENCES refers to the table to which the current table is getting linked, and column_name denotes the primary key column name of the referenced table.

Syntax (Oracle):

Create table table_name(
column_name1 datatype PRIMARY KEY,
column_name2 datatype,
column_name3 datatype FOREIGN KEY REFERENCES table_name(column_name)
);

Example Of Foreign Key

Continuing with the college record table, as mentioned in the example for the primary key, look at the additional/ new table called Library.

Student:

student_id	student_name	student_branch	student_emailid
100	Riya	CSE	riya@xyz.com
101	Aman	CSE	aman@xyz.com
102	Deepak	Mech	deepak@xyz.com
103	Barkha	IT	barkha@xyz.com

Library:

book_id	student_id	number_of _books_borrowed
200	100	9
201	101	15
202	102	7
203	103	0

The Library table represents the number of books borrowed by each student. To get the number of books borrowed by each student, we need to know the student ID for each student, too. Hence, student_id is also taken into account in the Library table, but it is the primary key for the Student table. In the same way, Book ID is the primary key for the Library table.

But what is the Student ID in the Library table? Student ID acts as a foreign key for the Library table. Here, student_id is being used to cross-reference both tables.

Conclusion

Primary keys and foreign keys are fundamental components of relational databases. Primary keys uniquely identify each record in a table, ensuring data integrity and optimizing retrieval. Foreign keys create relationships between tables, enforcing referential integrity and maintaining consistency across the database.

Together, these keys form the backbone of relational database design, supporting robust data management and efficient querying. Understanding the difference between primary key and foreign key is essential for designing efficient and reliable relational databases.

Also read: 40+ Frequently Asked DBMS Interview Questions With Answers (2024)

Frequently Asked Questions

Q. What is the relationship between a primary key and a foreign key?

A primary key defines a unique attribute of the table. And the foreign key defines the relationship between tables. The relationship between the primary key and foreign key is that the primary key of one table acts as the foreign key for another.

Q. Can there be more than one primary key in a single table?

No. By definition, the primary key is the principal key, which represents the main attribute of the row of the data. The idea behind the primary key is that it has to be unique to represent the attribute throughout the table. Hence, a single table cannot have more than one primary key.

Q. Can there be more than one foreign key in a single table?

Yes. The foreign key, as we know, is the attribute constraint that helps define the relationship between two tables. A single table can be related to more than one table in the database. It can thus have more than one foreign key.

Q. What is the difference between the primary key and the candidate key?

Both primary and candidate keys refer to attributes that help in the unique identification of data in a table/ relation. But they are not the same.

The core difference is that there can only be one primary key in a relation, and it acts as the minimal super key. However, in the case of a candidate key, there can be more than one candidate key in a relationship. Also, the candidate key attribute can have a NULL value, which is not the case for the primary key.

Q. Can a table have multiple primary keys?

No, a table cannot have multiple primary keys. A field or group of fields that have values that are distinctive throughout a table is known as a primary key, and there can only be one primary key for each table. However, there can only be one primary key, and it can contain as many columns as are required to identify your rows uniquely.

Q. How does a foreign key relate to two tables in a database?

A foreign key establishes a relationship between two tables by referencing the primary key in another table.

This creates a link that enforces referential integrity, ensuring that the value in the foreign key column matches a value in the referenced primary key column.
This relationship allows for the creation of parent-child relationships between tables, enabling complex queries and maintaining data consistency across the database.
The table containing the primary key is called the parent table, and the table in which it is used as a reference is called the child table.

Q. Why is it essential for a primary key to have unique values?

A table column that helps uniquely identify an individual row in the table is known as the primary key.

Unique values are essential for a primary key to ensure that each record in a table can be uniquely identified.
Because if a primary key column has duplicate values, it would not be possible to distinguish between the rows with the same values. This would lead to data inconsistency and make it difficult to retrieve specific data from the table.
The uniqueness hence prevents duplicate records, maintains data integrity, and allows for efficient data retrieval.

In short, without unique primary keys, distinguishing between records would be challenging, leading to potential data anomalies and inconsistencies within the database.

This compiles our discussion on the difference between primary key and foreign key. Here are a few other articles you must explore:

Shivani Goyal

Manager, Content

I am an economics graduate using my qualifications and life skills to observe & absorb what life has to offer. A strong believer in 'Don't die before you are dead' philosophy, at Unstop I am producing content that resonates and enables you to be #Unstoppable. When I don't have to be presentable for the job, I'd be elbow deep in paint/ pencil residue, immersed in a good read or socializing in the flesh.