Primary keys and foreign keys are important parts of relational databases. A primary key uniquely identifies each record in a table. A foreign key connects one table to another by referring to a key in another table. Together, they help databases maintain unique records, relationships, and data consistency.
For example, consider an online store.
The store may have:
Customers
Orders
Products
Payments
Each table stores different information.
Keys help connect these tables without storing the same information repeatedly.
What is a Primary Key?
A primary key is a column, or group of columns, that uniquely identifies each row in a table.
For example, a customer table may have:
customer_id
name
email
city
The customer_id can be the primary key.
Example:
customer_id = 101
name = Rahul
email = rahul@example.com
Another customer may have:
customer_id = 102
name = Priya
email = priya@example.com
Each customer gets a unique ID.
The database can use this ID to identify the exact customer.
Why is a Primary Key Important?
A primary key prevents two rows from having the same identity.
For example:
Customer 101
Customer 102
Customer 103
The database can clearly distinguish between them.
A primary key generally has these important properties:
- It must uniquely identify a record.
- It cannot contain duplicate values.
- It normally cannot contain
NULL.
- Each table has one primary key constraint.
- The key can contain one column or multiple columns.
For example:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(150)
);
Here, customer_id is the primary key.
What is a Foreign Key?
A foreign key is a column that references a key in another table.
It is mainly used to create relationships between tables.
Suppose we have a customers table:
customer_id
name
And an orders table:
order_id
customer_id
amount
The customer_id in the orders table can be a foreign key.
It points to:
customers.customer_id
The relationship looks like this:
Customers
|
| customer_id
↓
Orders
This tells the database which customer owns each order.
Simple Example
Suppose the customer table contains:
Customer ID: 101
Name: Rahul
The orders table contains:
Order ID: 5001
Customer ID: 101
Amount: $250
The foreign key tells us:
Order 5001
↓
Customer 101
↓
Rahul
The order does not need to store the customer's name again.
It only needs the customer's ID.
Primary Key vs Foreign Key: Simple Difference
The easiest way to remember the difference is:
Primary key identifies a record.
Foreign key connects a record to another table.
For example:
Customers Table
customer_id = 101
↑
|
|
customer_id = 101
Orders Table
The first customer_id identifies the customer.
The second customer_id creates the relationship between the order and customer.
How Primary Keys Create Uniqueness
Imagine a customer table without a primary key.
It could contain:
101 | Rahul
101 | Rahul
102 | Priya
Now the database has two records with the same customer ID.
It becomes difficult to determine which record represents the real customer.
A primary key prevents this situation.
For example:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100)
);
If the database already contains:
101 | Rahul
trying to insert another record with customer_id = 101 will violate the primary key constraint.
This protects data integrity.
How Foreign Keys Create Relationships
Foreign keys allow different tables to work together.
For example:
Customers
↓
Orders
↓
Order Items
↓
Products
The relationships may look like:
customers.customer_id
↓
orders.customer_id
orders.order_id
↓
order_items.order_id
products.product_id
↓
order_items.product_id
This creates a connected data model.
Each table can focus on its own information.
One-to-Many Relationship
One of the most common relationships is one-to-many.
For example:
One customer can have many orders.
The customer table may contain:
Customer 101
The orders table may contain:
Order 5001 → Customer 101
Order 5002 → Customer 101
Order 5003 → Customer 101
Here:
One Customer
↓
Many Orders
The customer's primary key becomes the foreign key in the orders table.
Foreign Key Example in SQL
The customer table can be created like this:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100)
);
The orders table can reference it:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
amount DECIMAL(10,2),
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
);
Now order_id uniquely identifies an order.
customer_id connects that order to a customer.
What is Referential Integrity?
Foreign keys help maintain referential integrity.
Referential integrity means that a foreign key should refer to a valid record in the referenced table, according to the database's constraints.
For example, suppose the customers table contains:
101
102
103
An order can reference:
101
because customer 101 exists.
But an order referencing:
999
may be rejected if customer 999 does not exist.
This prevents invalid relationships.
What Happens When a Parent Record Is Deleted?
Suppose customer 101 has several orders.
Now someone tries to delete customer 101.
The database needs to determine what should happen to those related orders.
Foreign key rules can define different behaviors.
Common options include:
RESTRICT
Prevent the customer from being deleted while related records exist.
CASCADE
Delete related records automatically.
For example:
Delete Customer
↓
Delete Related Orders
SET NULL
Keep the related record but set the foreign key to NULL, if the column allows it.
The appropriate behavior depends on the application's requirements.
Can a Foreign Key Contain Duplicate Values?
Yes.
This is an important difference.
A primary key must be unique.
A foreign key usually does not need to be unique.
For example:
Order 5001 → Customer 101
Order 5002 → Customer 101
Order 5003 → Customer 101
The value 101 appears multiple times in the orders table.
That is completely valid.
It represents the fact that one customer can have many orders.
Can a Table Have Multiple Foreign Keys?
Yes.
A table can have multiple foreign keys.
For example, an orders table could contain:
order_id
customer_id
product_id
employee_id
It might reference:
customer_id → customers.customer_id
product_id → products.product_id
employee_id → employees.employee_id
This allows one table to connect with several other tables.
Can a Primary Key Use Multiple Columns?
Yes.
This is called a composite primary key.
Suppose an order can contain multiple products.
An order_items table might contain:
order_id
product_id
quantity
The combination of:
order_id + product_id
could uniquely identify each item within an order.
For example:
Order 5001 + Product 20
Order 5001 + Product 30
Order 5002 + Product 20
Each combination can represent a unique relationship.
Natural Keys and Surrogate Keys
Primary keys can be designed in different ways.
A natural key comes from real-world data.
For example:
Email Address
Passport Number
Product SKU
A surrogate key is usually a generated identifier.
For example:
customer_id = 101
customer_id = 102
customer_id = 103
Surrogate keys are common because they are simple and stable.
For example:
customer_id INT PRIMARY KEY
The database may automatically generate these values.
Primary Key and Foreign Key in Data Analytics
These concepts are also important in analytics.
Suppose a data warehouse contains:
Customers
Sales
Products
The sales table may contain customer and product identifiers.
The relationships allow analysts to combine information.
For example:
Sales
↓
Customer ID
↓
Customers
An analyst can then answer questions such as:
- Which customers generated the most sales?
- Which products sell most often?
- Which regions generate the most revenue?
- How many orders does each customer have?
Keys make these relationships possible.
Using JOIN With Primary and Foreign Keys
SQL JOIN operations commonly use keys.
For example:
SELECT
customers.name,
orders.order_id,
orders.amount
FROM customers
JOIN orders
ON customers.customer_id = orders.customer_id;
Here:
customers.customer_id
=
orders.customer_id
The database uses the matching values to combine information.
The result can show:
Customer Name
Order ID
Order Amount
This is one of the most common uses of primary and foreign keys.
Practical E-Commerce Example
Imagine an online store with four tables:
Customers
Orders
Products
Payments
The relationships could look like:
Customers
|
| customer_id
↓
Orders
|
| order_id
↓
Payments
And:
Products
↑
|
order_items
|
↓
Orders
The keys connect the entire system.
For example:
Customer 101
↓
Order 5001
↓
Product 25
↓
Payment 9001
The database can follow these relationships to understand the complete transaction.
Common Mistakes With Keys
Several mistakes can cause database problems.
Using Duplicate Primary Keys
Two records should not have the same primary key.
Using Unstable Keys
A key should ideally remain stable over time.
Missing Foreign Key Constraints
Without proper constraints, invalid relationships may enter the database.
Using the Wrong Column
The foreign key should reference the correct key in the related table.
Ignoring NULL Rules
A foreign key may allow NULL in some designs, but this depends on the relationship requirements.
Deleting Parent Records Carelessly
Deleting a parent record can affect related child records.
Foreign key actions should therefore be designed carefully.
Simple Way to Remember
Think about a school database.
The students table might contain:
student_id
name
class
The student_id is the primary key.
The marks table might contain:
mark_id
student_id
subject
score
Here:
marks.mark_id
uniquely identifies each marks record.
And:
marks.student_id
connects the marks record to a student.
So:
Primary Key
↓
Who is this record?
Foreign Key
↓
Which other record is this connected to?
Final Thoughts
A primary key and a foreign key serve different but connected purposes.
The primary key provides a unique identity for records inside a table.
The foreign key creates a relationship between records in different tables.
They help databases maintain:
- Unique records
- Relationships
- Referential integrity
- Consistent data
- Efficient joins
- Organized data models
In simple terms:
Primary key = uniquely identifies a record.
Foreign key = connects that record to another table.
For example, customer_id can uniquely identify a customer in the customers table. The same customer_id can appear as a foreign key in the orders table to show which customer placed each order.
Together, these keys form the foundation of relationships in relational database systems.