Primary Key vs Foreign Key: Difference, Examples, Types, Uses in DBMS and SQL

Primary Key vs Foreign Key is one of the most important topics in DBMS, RDBMS, SQL and database design. Both primary keys and foreign keys are used when designing relational databases, but they perform different functions.

A Primary Key uniquely identifies each record in a database table, whereas a Foreign Key is mainly used to create a relationship between two tables. In simple words, a primary key identifies a record, while a foreign key connects related records stored in different tables.

Understanding the difference between primary key and foreign key is important for students of BCA, B.Tech, MCA, computer science and database management, as well as anyone learning SQL and relational database design.

In this detailed guide, we will explain primary key vs foreign key with simple examples, real-world applications, SQL syntax, advantages, limitations, referential integrity, types of keys, and a detailed parameter-wise comparison.

Also Read: What is DBMS? Database Management System

Related Topic: Database Normalization

What is a Primary Key in DBMS?

A Primary Key is a column, or a combination of columns, that uniquely identifies every row or record in a database table.

Every record in a relational database should have a way to be identified uniquely. A primary key provides that unique identity.

For example, consider the following Student table:

Student_ID	Student_Name	Course
101	Rahul	BCA
102	Amit	BCA
103	Neha	BCA

Here, Student_ID can be the primary key because each student has a unique value.

Characteristics of a Primary Key

A primary key uniquely identifies each record.
Duplicate values are not permitted.
A primary key cannot contain NULL values.
A table has one primary key constraint.
A primary key may consist of one column or multiple columns.
It helps maintain entity integrity.
It can be referenced by a foreign key.
It is commonly used when searching for a particular record.

What is a Foreign Key in DBMS?

A Foreign Key is a column or group of columns in one table that refers to a primary key or suitable unique key in another table.

The main purpose of a foreign key is to establish a logical relationship between tables and help maintain referential integrity.

For example, suppose we have a Customer table:

Customer_ID	Customer_Name
C101	Rahul
C102	Priya

Now consider an Orders table:

Order_ID	Customer_ID	Amount
O501	C101	1500
O502	C101	2500
O503	C102	900

Here, Customer_ID is the primary key in the Customer table and acts as a foreign key in the Orders table.

This relationship allows the database to determine which customer placed each order.

Primary Key vs Foreign Key: Detailed Difference

The following table provides a detailed comparison of primary key and foreign key based on important DBMS parameters.

Parameter	Primary Key	Foreign Key
1. Meaning	A primary key is a column or set of columns that uniquely identifies every record within a table. It gives each row a unique identity.	A foreign key is a column or set of columns that refers to a key in another table and is used to establish a relationship between related tables.
2. Main Purpose	Its main purpose is to uniquely identify records so that a particular row can be distinguished from every other row in the same table.	Its main purpose is to connect tables and maintain a valid relationship between records stored in parent and child tables.
3. Uniqueness	Values in a primary key must be unique within the table. Two records cannot have the same primary key value.	Foreign key values do not normally have to be unique. The same foreign key value can appear in many rows, such as when one customer has several orders.
4. NULL Values	A primary key cannot contain NULL because every record must have a valid unique identifier.	A foreign key may allow NULL when the relationship is optional and the column has not been defined as NOT NULL.
5. Number in a Table	A table can have only one primary key constraint, although that primary key may contain multiple columns.	A table can have multiple foreign key constraints, allowing it to establish relationships with one or more other tables.
6. Relationship	A primary key identifies records in its own table. It can also provide the key that another table references.	A foreign key creates a link from the child table to a parent table by referring to a primary or suitable unique key.
7. Integrity	Primary keys are associated with entity integrity because each record needs a unique and non-null identity.	Foreign keys are associated with referential integrity because they help ensure that references between tables remain valid.
8. Location	The primary key is defined in the table whose records need unique identification.	The foreign key is defined in the referencing or child table and points toward a key in the parent table.
9. Duplicate Values	Duplicate primary key values are not permitted because they would make unique identification impossible.	Duplicate foreign key values are generally allowed because multiple child records can belong to the same parent record.
10. Example	Student_ID in a Student table can uniquely identify every student.	Student_ID in an Enrollment table can refer to Student_ID in the Student table.
11. Parent Table	The primary key is often found in the parent table when that table is referenced by another table.	The foreign key normally exists in the child table and refers back to the parent table.
12. Child Table	A primary key identifies records in the table itself and does not depend on another table for its identity.	A foreign key is commonly used in the child table to identify which parent record a particular row belongs to.
13. SQL Constraint	It is declared using the PRIMARY KEY constraint in SQL.	It is declared using the FOREIGN KEY constraint, generally together with a REFERENCES clause.
14. Referential Integrity	The primary key provides the unique key that can be referenced by related tables.	The foreign key helps ensure that a child record does not reference a non-existing parent record when the constraint is enforced.
15. Table Dependency	A primary key can exist independently within its table.	A foreign key represents a dependency or relationship between the child table and the referenced table.
16. Composite Key	A primary key can be composed of multiple columns, known as a composite primary key.	A foreign key can also consist of multiple columns when it references a composite key.
17. Database Design	It forms an important part of identifying entities and designing tables in a relational database.	It forms an important part of connecting entities and representing relationships between tables.
18. SQL JOINs	A primary key often participates in joins because it identifies the parent record.	A foreign key commonly participates in joins because it contains the value that connects the child record to the parent record.
19. Real-World Example	A Student_ID, Employee_ID, Account_ID or Product_ID can uniquely identify a record.	Customer_ID in an Orders table or Department_ID in an Employee table can connect related records.
20. Simple Meaning	Primary Key = Who or which record is this? It provides the unique identity of the row.	Foreign Key = Which related record does this belong to? It provides the connection between tables.

Primary Key and Foreign Key Example in a College Database

Consider a college database containing three tables: Student, Department and Enrollment.

Student Table

The Student table may contain Student_ID as its primary key.

Department Table

The Department table may contain Department_ID as its primary key.

Enrollment Table

The Enrollment table can contain Student_ID and Department_ID as foreign keys. These keys connect enrollment records to the appropriate student and department records.

This approach avoids unnecessarily repeating complete student and department information in every enrollment record. Instead, related information can be retrieved using SQL queries and JOIN operations.

Primary Key vs Foreign Key SQL Example

The following example demonstrates how primary and foreign key constraints can be created in SQL:

CREATE TABLE Student (
    Student_ID INT PRIMARY KEY,
    Student_Name VARCHAR(100),
    Course VARCHAR(50)
);

CREATE TABLE Enrollment (
    Enrollment_ID INT PRIMARY KEY,
    Student_ID INT,
    Subject VARCHAR(100),

    FOREIGN KEY (Student_ID)
    REFERENCES Student(Student_ID)
);

In this example:

Student_ID in Student is the primary key.
Enrollment_ID in Enrollment is the primary key.
Student_ID in Enrollment is the foreign key.
The foreign key references Student(Student_ID).

How Primary Key and Foreign Key Work Together

Primary and foreign keys work together to create relationships between tables.

The process can be understood in four simple steps:

The parent table contains a primary key.
The child table contains a foreign key.
The foreign key refers to the parent table's key.
The database can then maintain the relationship between the two tables.

Customer Table → Customer_ID = Primary Key

Orders Table → Customer_ID = Foreign Key

This means that the Orders table can associate each order with a customer.

What is Referential Integrity?

Referential integrity is a database concept that helps maintain valid relationships between related tables.

For example, if an Orders table contains Customer_ID = 105, the referenced Customer table should contain the corresponding customer record when the foreign key constraint requires such a match.

This helps prevent invalid references and improves the consistency of relational database data.

Important: The exact behavior of foreign keys during INSERT, UPDATE and DELETE operations depends on the database system and the referential actions configured, such as CASCADE, SET NULL or RESTRICT/NO ACTION.

Primary Key vs Foreign Key in Real-Life Applications

1. Banking Database

An Account_ID can uniquely identify an account in an Account table. A transaction table can use Account_ID as a foreign key to associate transactions with the correct account.

2. E-Commerce Database

Product_ID can uniquely identify products. An Order_Details table can use Product_ID as a foreign key to connect purchased products with their product information.

3. College Management System

Student_ID can uniquely identify students. Course registration tables can use Student_ID as a foreign key to connect students with their registered courses.

4. Hospital Database

Patient_ID can identify patients. An Appointment table can use Patient_ID as a foreign key to associate appointments with patients.

5. Employee Management System

Employee_ID can identify employees. A Payroll table can use Employee_ID as a foreign key to associate salary records with employees.

Advantages of Primary Key

Provides unique identification of records.
Prevents duplicate key values.
Prevents NULL values in the key.
Supports entity integrity.
Helps establish relationships with other tables.
Improves the organization of relational data.
Provides a reliable reference point for SQL queries.

Advantages of Foreign Key

Creates relationships between database tables.
Supports referential integrity.
Helps prevent invalid references when constraints are enforced.
Reduces unnecessary duplication of related data.
Supports relational database design.
Works effectively with SQL JOIN operations.
Helps represent parent-child relationships.

Limitations and Considerations

Primary Key Considerations

The key should provide stable and unique identification.
Changing a primary key can affect related foreign key records.
A poorly chosen primary key can complicate database design.

Foreign Key Considerations

Foreign key relationships must be designed carefully.
Deleting or updating parent records can affect child records depending on the configured referential actions.
Large numbers of relationships can make database design more complex.

Primary Key vs Foreign Key vs Candidate Key

Key	Main Purpose	Duplicate Values	Relationship
Primary Key	Uniquely identifies records.	Not allowed.	Can be referenced by foreign keys.
Foreign Key	Connects related tables.	Generally allowed.	References a key in another table.
Candidate Key	A minimal set of attributes that can uniquely identify records and could be selected as the primary key.	Not allowed within the candidate-key values.	A candidate key may be selected as the primary key.

Common Mistakes About Primary Key and Foreign Key

Mistake 1: Thinking Foreign Keys Must Be Unique

A foreign key does not normally need to be unique. For example, one customer can place many orders, so the same Customer_ID can appear in multiple rows of the Orders table.

Mistake 2: Thinking Every Table Must Have a Foreign Key

Not every table requires a foreign key. A table can exist independently without referencing another table.

Mistake 3: Confusing Primary Key With Unique Key

A primary key is specifically chosen to identify records in a table. A unique key is used to enforce uniqueness on another column or set of columns. Their exact NULL behavior can vary by database system.

Mistake 4: Thinking Primary Key and Foreign Key Have the Same Purpose

They do not. A primary key provides identity, while a foreign key establishes a relationship with another table.

Primary Key vs Foreign Key: Easy Trick to Remember

PRIMARY KEY = IDENTIFY

A Primary Key uniquely identifies a record.

FOREIGN KEY = CONNECT

A Foreign Key connects related records between tables.

Frequently Asked Questions

What is a primary key in DBMS?

A primary key is a column or combination of columns that uniquely identifies each record in a relational database table.

What is a foreign key in DBMS?

A foreign key is a column or combination of columns that refers to a key in another table and helps establish a relationship between tables.

What is the main difference between primary key and foreign key?

The primary key uniquely identifies records within its table, while the foreign key is used to connect records between related tables.

Can a primary key contain duplicate values?

No. Duplicate values are not allowed in a primary key because it must uniquely identify each record.

Can a foreign key contain duplicate values?

Yes. Duplicate foreign key values are normally allowed. For example, several orders can have the same Customer_ID.

Can a foreign key contain NULL?

It can, if the foreign key column allows NULL and the relationship is optional. The exact behavior depends on the database definition.

Can one table have multiple foreign keys?

Yes. A table can contain multiple foreign keys that reference the same or different tables.

Can a primary key be a foreign key?

Yes. In some database designs, the same column can participate as a primary key in one table and as a foreign key referencing another table. This is common in certain one-to-one relationships.

What is referential integrity in DBMS?

Referential integrity is the principle of keeping relationships between related tables valid. Foreign key constraints are commonly used to enforce this integrity.

Why are primary keys and foreign keys important?

Primary keys provide unique identification, while foreign keys establish relationships between tables. Together, they form an important foundation of relational database design.

Conclusion

The difference between Primary Key and Foreign Key is fundamental to understanding relational databases and DBMS. A primary key gives each record a unique identity, whereas a foreign key connects records between related tables.

A simple way to remember the concept is:

Primary Key → Uniquely identifies a record.

Foreign Key → Creates a relationship between tables.

Once you understand primary keys and foreign keys, concepts such as candidate keys, composite keys, normalization, SQL JOINs, referential integrity, database relationships and relational database design become much easier to understand.

Next Recommended Article: Primary Key vs Foreign Key vs Candidate Key vs Super Key — Complete Difference in DBMS.

Labels

Wednesday, 7 October 2026