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.

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:

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:

1. 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.
2. 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:

Library:

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:

1. Data Independence In DBMS - Understand With Examples
2. Data Models In DBMS: Types, Uses And More
3. Decoding Data Redundancy In DBMS | Causes, Advantages, Solutions
4. Difference Between CHAR And VARCHAR Data Types In SQL Server
5. DBMS: Key Differences Between Primary Key And Candidate Key