Loading
JAVA
SPRING
ANGULAR
SQL
HTML
CSS
JAVASCRIPT
Data Structure
Foreign Keys
VideosDiscussion
In relational databases, data is often distributed across multiple tables to avoid duplication and improve organization.
To maintain meaningful connections between these tables, SQL uses Foreign Keys.

A Foreign Key ensures that relationships between tables remain valid and consistent, even when data changes.


What Is a Foreign Key?

A Foreign Key is a column (or group of columns) in one table that refers to the Primary Key of another table.

It enforces a rule called referential integrity, which ensures that:

  • A value in the child table must exist in the parent table
  • Invalid references are not allowed


Example

Parent Table: departments

dept_iddept_name
101Marketing


Chile Table: employees

emp_idemp_namedept_id
01Rahul Kumar101

Here, dept_id in the employees table is a Foreign Key referencing dept_id in the departments table.


Example: Creating a Foreign Key

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);


By default, SQL enforces strict referential integrity.

This means:

  • A parent record cannot be deleted if child records exist
  • A primary key cannot be updated if it is referenced elsewhere
While this behavior protects data, it can sometimes limit flexibility in real-world scenarios.


Cascading Foreign Keys provide a controlled and automatic way to handle changes in related tables.

They allow the database to:

  • Automatically update related records
  • Automatically delete dependent records
  • Maintain consistency without manual intervention
Cascading ensures that related data remains synchronized, even when changes occur in the parent table.


What Are Cascading Foreign Keys?

A Cascading Foreign Key automatically performs an action on the child table when a related action occurs on the parent table.

These actions are defined using:

  • ON DELETE
  • ON UPDATE


Types of Cascading

1. ON DELETE CASCADE

When a record in the parent table is deleted, all related records in the child table are also deleted automatically.


Example

FOREIGN KEY (dept_id)
REFERENCES departments(dept_id)
ON DELETE CASCADE;

Effect:
Deleting a department automatically deletes all employees belonging to that department.


2. ON UPDATE CASCADE

When a primary key value in the parent table is updated, the related foreign key values in the child table are updated automatically.


Example

FOREIGN KEY (dept_id)
REFERENCES departments(dept_id)
ON UPDATE CASCADE;


Example: Cascading in Actions

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id)
    REFERENCES departments(dept_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);


Important Points to Remember

  • Cascading actions help maintain referential integrity
  • They reduce manual cleanup of dependent records
  • Use cascading deletes carefully to avoid accidental data loss
  • Not all databases behave identically; always verify DB support


Two Minute Drill

  • Foreign Key links child table to parent table
  • Enforces referential integrity
  • Cascading automates delete and update actions
  • ON DELETE CASCADE removes dependent records
  • ON UPDATE CASCADE updates related values
  • Cascading must be defined explicitly