MySQL On Delete Cascade

The MySQL ON DELETE CASCADE option is used when defining foreign key constraints to specify what should happen when a record in the parent table (referenced table) is deleted. When you define a foreign key constraint with ON DELETE CASCADE, it ensures that if a row in the parent table is deleted, all related rows in the child table(s) are automatically deleted as well, cascading the delete operation.

Here’s how to create a foreign key constraint with ON DELETE CASCADE:

Suppose you have two tables, orders and order_items, with a foreign key relationship. The order_items table references the orders table.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE
);

CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    item_name VARCHAR(255),
    item_quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE
);

In the order_items table definition, the foreign key constraint is specified with ON DELETE CASCADE. This means that if you delete a row in the orders table, all related rows in the order_items table that reference the deleted order will also be deleted.

For example, if you have an order with order_id equal to 1 and there are corresponding order items in the order_items table, if you delete that order from the orders table, all related order items will be automatically deleted as well.

It’s important to be cautious when using ON DELETE CASCADE, as it can lead to unintended data loss if you delete records without realizing the cascade effect. Be sure to understand the relationships in your database and use this option when appropriate to maintain data integrity and consistency.