Foreign Keys
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_id | dept_name |
|---|---|
| 101 | Marketing |
Chile Table: employees
| emp_id | emp_name | dept_id |
|---|---|---|
| 01 | Rahul Kumar | 101 |
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
Example: Cascading in Actions
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
Need more clarification?
Drop us an email at career@quipoinfotech.com