CASCADE in SQL

CASCADE in SQL

In SQL, CASCADE is an option used with FOREIGN KEY constraints when defining relationships between tables. It specifies that certain actions on a parent table should automatically propagate to the related child table(s). The most common use cases are ON DELETE CASCADE and ON UPDATE CASCADE.

Let’s break it down:

1. ON DELETE CASCADE

If a row in the parent table is deleted, all corresponding rows in the child table are automatically deleted.

Example:

-- Create parent table
CREATE TABLE Department (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50)
);

-- Create child table
CREATE TABLE Employee (
    EmpID INT PRIMARY KEY,
    EmpName VARCHAR(50),
    DeptID INT,
    FOREIGN KEY (DeptID) REFERENCES Department(DeptID) ON DELETE CASCADE
);

-- Insert data
INSERT INTO Department VALUES (1, 'HR'), (2, 'IT');
INSERT INTO Employee VALUES (101, 'Alice', 1), (102, 'Bob', 1), (103, 'Charlie', 2);

-- Delete a department
DELETE FROM Department WHERE DeptID = 1;

-- Result:
-- Department with DeptID=1 is deleted
-- Employees Alice and Bob are also automatically deleted

2. ON UPDATE CASCADE

If the primary key of a parent table is updated, all corresponding foreign key values in the child table are automatically updated.

Example:

-- Update the primary key in Department
UPDATE Department SET DeptID = 20 WHERE DeptID = 2;

-- Result:
-- The DeptID in Employee table for Charlie is automatically updated from 2 to 20

Key Notes:

  • CASCADE helps maintain referential integrity without manually updating or deleting child records.
  • Without CASCADE, deleting or updating a parent row would fail if child rows exist (unless you use SET NULL or SET DEFAULT).

Leave a Reply