MySQL ON DELETE CASCADE

The ON DELETE CASCADE is a referential action that is used in foreign key constraints to automatically delete rows in a child table when the corresponding row in the parent table is deleted. This helps maintain data integrity and ensures that there are no orphaned records in the child table.

Syntax

When creating or altering a table, you can specify ON DELETE CASCADE as part of a foreign key constraint:

CREATE TABLE child_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES parent_table(id) ON DELETE CASCADE
);

parent_table: The parent table that holds the referenced data.
child_table: The table that contains the foreign key reference.
parent_id: The foreign key column in the child table references the parent table.
id: The primary key column in the parent table that is being referenced.

Alternatively, if you want to add ON DELETE CASCADE to an existing foreign key constraint:

ALTER TABLE child_table
ADD CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES parent_table(id) ON DELETE CASCADE;

How It Works

When a row in the parent table is deleted, all corresponding rows in the child table that reference the deleted row are also automatically deleted. This is called cascading delete.

For example, if you have an orders table (child table) and a customers table (parent table), and a foreign key from orders references customers, then if a customer is deleted, all their associated orders will be deleted automatically.

Example

1. Creating Parent and Child Tables with ON DELETE CASCADE

-- Create the parent table
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- Create the child table with a foreign key that references the parent table
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
);

In this case, if a customer is deleted from the customers table, all their corresponding orders in the orders table will be automatically deleted.

2. Inserting Data into Parent and Child Tables

-- Insert into the customers table
INSERT INTO customers (name) VALUES ('John Doe');
INSERT INTO customers (name) VALUES ('Jane Smith');

-- Insert into the orders table
INSERT INTO orders (customer_id, order_date) VALUES (1, '2025-01-20');
INSERT INTO orders (customer_id, order_date) VALUES (2, '2025-01-19');

3. Deleting a Row in the Parent Table

If we delete a customer from the customers table:

-- Delete the customer with customer_id = 1
DELETE FROM customers WHERE customer_id = 1;

The ON DELETE CASCADE ensures that all orders associated with customer_id = 1 in the orders table are also deleted.

4. Verify the Deletion

-- Check remaining orders
SELECT * FROM orders;

After the deletion, only the order associated with Jane Smith will remain in the orders table, and the orders associated with John Doe will have been automatically deleted.

Behavior of ON DELETE CASCADE

Data Integrity: Ensures that there are no orphaned rows in the child table when the referenced row in the parent table is deleted.
Cascading Effect: The cascading effect can extend to multiple levels if multiple foreign key relationships with ON DELETE CASCADE are set.

Example of Multi-level Cascade

-- Create grandchild table with foreign key referencing child table
CREATE TABLE payments (
    payment_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    amount DECIMAL(10,2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE
);

If a customer is deleted, their orders will be deleted (ON DELETE CASCADE on orders), and then all associated payments will be deleted as well (ON DELETE CASCADE on payments).

Other Referential Actions

While ON DELETE CASCADE is useful, other referential actions can also be used with foreign keys to specify different behaviors when a parent record is deleted. These include:

ON DELETE SET NULL: Sets the foreign key value in the child table to NULL when the parent row is deleted.
ON DELETE RESTRICT: Prevents the deletion of a parent row if there are corresponding rows in the child table.
ON DELETE NO ACTION: Similar to RESTRICT; it does not allow the deletion of the parent row if it has dependent child rows.
ON DELETE SET DEFAULT: Sets the foreign key column in the child table to its default value when the parent row is deleted.

Conclusion

The ON DELETE CASCADE feature in MySQL is essential for maintaining referential integrity in relational databases. It automatically handles the deletion of related rows in child tables when the parent row is deleted, ensuring that no orphaned or invalid data remains in the database. This feature is particularly useful in scenarios involving parent-child relationships, such as customers and orders, employees and departments, etc.

Souy Soeng

MySQL ON DELETE CASCADE