Foreign Key in SQL: Definition and Examples

A foreign key in SQL is a column (or a set of columns) that links one table to the primary key of another table. It creates a relationship between two tables and helps maintain data consistency. In relational databases, where data is spread across multiple tables, these keys connect related records and prevent invalid entries. In this blog, you will learn what this concept is, why it matters, and how to create and manage it effectively with examples.

Table of Contents:

• What is a Foreign Key in SQL?
• SQL Foreign Key Syntax
• Types of Foreign Keys
• How to Create a Foreign Key in SQL (Step-by-Step Guide)
• Managing Foreign Keys in SQL
• Primary Key vs Foreign Key in SQL
• Common Challenges While Using Foreign Keys in SQL
• Best Practices for Using Foreign Key
• Real-World SQL Foreign Key Example
• Conclusion

What is a Foreign Key in SQL?

A foreign key in SQL is a column (or group of columns) in a table that is a reference to the primary key in another table. It establishes a relationship between two tables. The table that has this key is called the child table, and the table that has the primary key is called the parent table.

Importance of Foreign Keys in SQL Databases

Understanding the importance of this key will help you design more reliable and consistent databases:

• Ensure Data Integrity: They ensure that values entered in the child table must exist in the parent table.
• Avoid Orphan Records: You cannot enter a record that points to a non-existent parent table.
• Support Table Relationships: These keys are used to express one-to-one, one-to-many, or many-to-many relationships.
• Enhance Data Structure: The keys help organize data, making it manageable and scalable.

SQL Foreign Key Syntax

Let’s see the syntax used to define this relationship:

Syntax:

CREATE TABLE ChildTable (
    Column1 DataType,
    Column2 DataType,
    ...
    FOREIGN KEY (ColumnName) REFERENCES ParentTable(PrimaryKeyColumn)
);

Master SQL & Land Your Dream Data Job

Join our expert-led, hands-on SQL course designed to take you from beginner to pro

Explore Program

Types of Foreign Keys

1. Simple Foreign Key

This is the most common type. A simple foreign key is defined as a one-column relation from one table that references the primary key of a second table.

Let’s consider two tables: Departments as the parent table and Employees as the child table. A foreign constraint is defined on the DeptID column in the Employees table to link it with the DeptID column in the Departments table, as shown in the example below:

Example:

-- Parent Table
CREATE TABLE Departments (
  DeptID INT PRIMARY KEY,
  DeptName VARCHAR(50)
);
-- Child Table
CREATE TABLE Employees (
  EmpID INT PRIMARY KEY,
  EmpName VARCHAR(50),
  DeptID INT,
  FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

Explanation:In the above query, the DeptID column in the Employees table references the Departments table. This relationship ensures that every employee is linked to an existing department.

2. Composite Foreign Key

A composite foreign key uses two or more columns to define the relationship. This relationship is used when a single column is not sufficient to uniquely identify a record.

Example:

-- Parent Table
CREATE TABLE CourseSchedule (
  CourseID INT,
  SemesterID INT,
  PRIMARY KEY (CourseID, SemesterID)
);
-- Child Table
CREATE TABLE StudentEnrollments (
  StudentID INT,
  CourseID INT,
  SemesterID INT,
  FOREIGN KEY (CourseID, SemesterID) REFERENCES CourseSchedule(CourseID, SemesterID)
);

Explanation: This query defines CourseSchedule as the parent table with a composite primary key (CourseID, SemesterID). The StudentEnrollments table links to these columns, ensuring students register only for scheduled course-semester pairs.

3. Foreign Key with Cascading Actions

This type of constraint can include ON DELETE CASCADE or ON UPDATE CASCADE actions, which define what should happen to the child record when the corresponding entry in the parent table is deleted or updated

Example:

-- Parent Table
CREATE TABLE Customers (
  CustomerID INT PRIMARY KEY,
  CustomerName VARCHAR(50)
);

-- Child Table
CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  CustomerID INT,
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE CASCADE
);

Explanation: This query defines a relationship between the Orders and Customers tables using ON DELETE CASCADE. It ensures that when a customer is removed, all their related orders are automatically deleted, preventing orphan records.

How to Create a Foreign Key in SQL (Step-by-Step Guide)

Let’s explore how to create this relationship step-by-step with a practical example.

Step 1: Create the Parent Table

The Courses table is created to store course details. The CourseID column is defined as the primary key to ensure that each course has a unique identifier.

CREATE TABLE Courses (
  CourseID INT PRIMARY KEY,
  CourseName VARCHAR(50)
); 

Step 2: Create the Child Table

The Students table holds student details, and the CourseID column links each student to a course in the Courses table, creating a clear relationship between them.

CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  StudentName VARCHAR(50),
  CourseID INT,
  FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)

);

Now you might be wondering how to add this relationship in SQL to a table that already exists. Adding such a constraint connects related tables, keeps data accurate, and ensures only valid values appear in the child table. This approach is helpful when your tables are already created and you want to link them without rebuilding everything.

Example:

-- Parent table
CREATE TABLE Departments (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50)
);

-- Existing child table
CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    EmpName VARCHAR(50),
    DeptID INT
);

ALTER TABLE Employees
ADD CONSTRAINT fk_dept
FOREIGN KEY (DeptID) REFERENCES Departments(DeptID);

Explanation: Here, the ALTER TABLE command adds a foreign constraint to the existing Employees table, connecting DeptID in Employees to DeptID in Departments to ensure the data stays correct.

Managing Foreign Keys in SQL

These relationships link tables together and ensure data remains consistent. Here, you’ll learn how to handle, update, or remove them to maintain an organized database structure.

1. Dropping a Foreign Key Constraint in SQL

This process removes an existing constraint from a table using the ALTER TABLE statement. It’s useful when you need to modify or delete the relationship between two tables to update the database structure.

Syntax:

ALTER TABLE table_name
DROP FOREIGN KEY constraint_name;

2. Using ON DELETE and ON UPDATE with Foreign Keys in SQL

In SQL, when a record in the parent table is updated or deleted, we can define what changes must be made on the child table with the help of ON DELETE and ON UPDATE actions.

Syntax:

FOREIGN KEY (ColumnName)
REFERENCES ParentTable(PrimaryKeyColumn)
ON DELETE CASCADE
ON UPDATE CASCADE

Common Actions:

1. CASCADE: Automatically updates or deletes related records.
2. SET NULL: Sets the foreign key value to NULL in the child table.
3. SET DEFAULT: Sets the value to a default (must be defined earlier).
4. RESTRICT / NO ACTION: Prevents the action if child rows exist.

3. Using Foreign Keys to Normalize SQL Databases

Data normalization is a process of reducing repetitive data, which helps to improve efficiency. This key helps to achieve normalization by linking smaller and related tables.

Example:

CREATE TABLE Departments (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(100)
);

CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    EmpName VARCHAR(100),
    DeptID INT,
    FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

Explanation: In this example, DeptID in the Employees table acts as a reference to the department data, ensuring employees are correctly associated with their departments without repeating department name.

Primary Key vs Foreign Key in SQL

Let us explore the difference between a Primary key and Foreign key in SQL.

Common Challenges While Using Foreign Keys in SQL

1. Insert Restrictions: If the parent table does not have a matching value, the child table cannot insert that value. This helps keep the data accurate and connected.
2. Delete Restrictions: You cannot delete a record from the parent table if it is being used in the child table, unless the foreign key is set with ON DELETE CASCADE to remove related records automatically.
3. Complex Joins: Queries with multiple joins due to foreign keys can become slow and harder to read.
4. Circular References: It is not uncommon for two tables to reference each other using foreign keys, which can complicate the data relationships.

 Best Practices for Using Foreign Key

1. Meaningful Names for Constraints: When you create this type of constraint, give it a clear, descriptive name so that users can easily understand its purpose.
2. Avoid NULLs Unless Necessary: If your data model doesn’t require them, avoid using NULL values in key columns unless necessary.
3. Add Indexes Where Appropriate: You can create indexes on these columns to improve query performance and speed up lookups.
4. Test with Sample Data: Before applying these relationships to production data, test them with sample inserts, updates, and deletes to ensure everything behaves as expected.

Real-World SQL Foreign Key Example

Below are some real-world scenarios where these relationships play a vital role in maintaining data accuracy and consistency between tables.

1. E-Commerce System:
In an e-commerce database, key relationships connect the Customers, Orders, and Order Details tables.

They ensure that:

• Every order belongs to a valid customer.
• Each order detail is linked to an existing order.
• This setup prevents invalid entries, such as orders without customers or order details that don’t belong to any order.

2. Banking System:
In a banking database, referential links connect the Customers, Accounts, and Transactions tables.

They ensure that:

• Every transaction is tied to an existing account.
• Each account belongs to a valid customer.
• This structure maintains the integrity of financial data and prevents mismatched or orphaned records.

3. Hospital Management System:
In healthcare databases, constraints link the Doctors, Patients, and Appointments tables.

They ensure that:

• Every appointment refers to an existing doctor and patient.
• No records are created for doctors or patients who don’t exist in the system.
• This approach keeps medical records accurate and consistent.

Start Learning SQL for Free

Build your foundation in SQL with our beginner-friendly, zero-cost course.

Explore Program

Conclusion

In relational databases, these constraints are essential for building structured and reliable systems. They maintain relationships between tables by linking related data and ensuring consistency across the database. Whether you’re managing student enrollments, customer orders, or employee records, these relationships keep the data organized and meaningful. When implemented correctly, they help you retrieve cleaner data, simplify maintenance, and strengthen your overall database design. This makes your system more reliable and easier to scale. In this blog, you learned about this key concept, its role in maintaining data integrity, and how to create and manage it effectively.

Take your skills to the next level by enrolling in the SQL Course today and gaining hands-on experience. Also, prepare for job interviews with SQL Interview Questions prepared by industry experts

Check out our other blogs related to SQL:

Foreign Key in SQL – FAQs