View Categories

What is a foreign key?

Parthiban Kannan
Updated on December 2, 2025

1 min read

A foreign key is a column or a set of columns in one table that references the primary key in another table. Its main purpose is to maintain referential integrity between two related tables, ensuring that the relationship between records is consistent. Essentially, it enforces that the value in the foreign key column must exist in the referenced primary key column.

Example:
Suppose we have two tables: Customers and Orders. Each order is placed by a customer.

🗃️
filename.sql 
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(50)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Here, CustomerID in the Orders table is a foreign key referencing CustomerID in Customers. This ensures that every order is linked to a valid customer.

Practical application:
I’ve used foreign keys extensively in database design to model relationships, like:

  • Linking Employees to Departments
  • Linking Orders to Customers
  • Linking Invoices to Products

They help in joining tables efficiently for reports and analytics and prevent orphan records (e.g., an order without a valid customer).

Challenges faced:

  • One common challenge is handling deletes or updates in parent tables. For example, if a customer is deleted, the related orders can cause a foreign key constraint error. I’ve addressed this by using ON DELETE CASCADE or carefully designing business rules to prevent accidental deletions.
  • Another challenge is performance impact on very large tables during insert/update operations, as the database needs to check referential integrity.

Limitations:

  • Foreign keys enforce integrity but can slow down bulk insert or update operations.
  • Some NoSQL databases don’t support foreign keys, so relationships must be managed in application logic.

Alternative:

  • If strict referential integrity is not required, we can manage relationships through application-level validation instead of database-enforced foreign keys.
Scroll to Top