Database.Beginner.What is ON DELETE CASCADE?

When you set up a foreign key with ON DELETE CASCADE, it means:

• If you delete a row in the parent table (the table with the primary key), all related rows in the child table (the table with the foreign key) are automatically deleted.

This keeps the database consistent and avoids “orphan” records (child records without a parent).

CREATE TABLE Users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
    ON DELETE CASCADE
);

How it works:

• Users is the parent table.
• Orders is the child table.
• user_id in Orders references user_id in Users.
• If you delete a user, all their orders are deleted too.

Sample Data:

INSERT INTO Users (user_id, username) VALUES (1, 'Alice'), (2, 'Bob');

INSERT INTO Orders (order_id, user_id, order_date) VALUES 
(101, 1, '2025-06-01'),
(102, 1, '2025-06-02'),
(103, 2, '2025-06-03');

Tables now:
Users

user_id	username
1	Alice
2	Bob

Orders

order_id	user_id	order_date
101	1	2025-06-01
102	1	2025-06-02
103	2	2025-06-03

---

Now, if we run:

DELETE FROM Users WHERE user_id = 1;

What happens?

• Alice is deleted from Users.
• Automatically, both her orders (order_id 101 and 102) are deleted from Orders because of ON DELETE CASCADE.

---

Resulting tables:
Users

user_id	username
2	Bob

Orders

order_id	user_id	order_date
103	2	2025-06-03

---

Without ON DELETE CASCADE

If you didn’t have ON DELETE CASCADE and tried to delete Alice:

• The database would throw an error because there are Orders rows that still reference her user_id.