ON DELETE: CASCADE, RESTRICT and SET NULL
45 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)A customer is deleted, but their orders still point to them. Should the delete be blocked, should the orders disappear too, or should they stay without a customer? Each choice has a cost.
What happens to children when a parent is deleted
A referential action is the rule a foreign key follows when its parent row is deleted (ON DELETE) or its key value changes (ON UPDATE). The choices are RESTRICT, NO ACTION, CASCADE and SET NULL.
Deleting a customer who has orders has to do something with those orders. Example 1 reads the rule that each link of the practice database follows.
Example 1 · The rules in the practice database
SELECT constraint_name, table_name, referenced_table_name, update_rule, delete_ruleFROM information_schema.referential_constraintsWHERE constraint_schema = DATABASE()ORDER BY table_name, constraint_name;+--------------------+-------------+-----------------------+-------------+-------------+ | CONSTRAINT_NAME | TABLE_NAME | REFERENCED_TABLE_NAME | UPDATE_RULE | DELETE_RULE | +--------------------+-------------+-----------------------+-------------+-------------+ | categories_ibfk_1 | categories | categories | NO ACTION | NO ACTION | | employees_ibfk_1 | employees | departments | NO ACTION | NO ACTION | | employees_ibfk_2 | employees | employees | NO ACTION | NO ACTION | | enrollments_ibfk_1 | enrollments | students | NO ACTION | NO ACTION | | enrollments_ibfk_2 | enrollments | courses | NO ACTION | NO ACTION | | order_items_ibfk_1 | order_items | orders | NO ACTION | NO ACTION | | order_items_ibfk_2 | order_items | products | NO ACTION | NO ACTION | | orders_ibfk_1 | orders | customers | NO ACTION | NO ACTION | | products_ibfk_1 | products | categories | NO ACTION | NO ACTION | +--------------------+-------------+-----------------------+-------------+-------------+
What this code does
information_schema.referential_constraintshas one row per foreign key, with itsupdate_ruleanddelete_rule.- The practice script wrote no rules, so every foreign key shows
NO ACTION: deleting a parent with children is refused.
You add it at the end of the foreign key line: FOREIGN KEY (order_id) REFERENCES orders (order_id) ON DELETE CASCADE.
The question is real in every app. An admin deletes an order: what happens to its items? A customer closes their account: what happens to their orders, which the shop needs for its accounts and GST records? Each link needs its own answer. If you write no rule, MySQL uses NO ACTION: the delete is refused while children exist. To delete a row, write DELETE FROM table WHERE key = value;; the Changing Data module covers DELETE in full.
RESTRICT and NO ACTION
RESTRICT refuses to delete (or change the key of) a parent row that still has children, with ERROR 1451. In MySQL, NO ACTION behaves the same way and is the default when you write no rule.
Customer 10 has no orders and customer 1 has several. Example 2 deletes both under the default rule.
Example 2 · RESTRICT in action: customers 10 and 1
-- Customer 10 (Imran Khan) has no ordersDELETE FROM customers WHERE customer_id = 10;SELECT COUNT(*) AS customers_left FROM customers;
-- Customer 1 (Asha Rao) has three ordersDELETE FROM customers WHERE customer_id = 1;+----------------+ | customers_left | +----------------+ | 9 | +----------------+ ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`practice`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`))
What this code does
- Deleting a parent with no children works: 9 customers are left.
- Asha has orders, so
orders_ibfk_1(NO ACTION) refuses the delete with error 1451, and nothing changes. The message shows your own database name, which is why this is "Sample output". - Run the practice script again (
SOURCE sample-database.sql) to get customer 10 back.
RESTRICT is the safe choice: nothing disappears by accident. To delete such a parent, you must first decide about each child yourself: delete it, move it to another parent, or keep the parent after all. A parent without children can always be deleted.
Order of execution
Delete a customer safely
Customer 1 has orders and order items, and every link uses RESTRICT. Put the steps in the order that works.
CASCADE and its risks
ON DELETE CASCADE makes MySQL delete the child rows automatically when their parent row is deleted. Use it only when the children are part of the parent and mean nothing alone.
Example 3 deletes an order and its items go with it. Example 4 shows how a chain of cascades can erase a whole sales history.
Example 3 · CASCADE: an order is deleted
CREATE TABLE sale_orders ( order_id INT PRIMARY KEY, customer VARCHAR(40) NOT NULL);
CREATE TABLE sale_items ( item_id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, product VARCHAR(40) NOT NULL, qty INT NOT NULL, CONSTRAINT fk_sale_items_order FOREIGN KEY (order_id) REFERENCES sale_orders (order_id) ON DELETE CASCADE);
INSERT INTO sale_orders VALUES (1, 'Asha Rao'), (2, 'Ravi Kumar');INSERT INTO sale_items (order_id, product, qty) VALUES (1, 'boAt Airdopes 141', 2), (1, 'Redmi Note 13', 1), (2, 'Clean Code', 1);
DELETE FROM sale_orders WHERE order_id = 1;
SELECT * FROM sale_orders ORDER BY order_id;SELECT * FROM sale_items ORDER BY item_id;+----------+------------+ | order_id | customer | +----------+------------+ | 2 | Ravi Kumar | +----------+------------+ +---------+----------+------------+-----+ | item_id | order_id | product | qty | +---------+----------+------------+-----+ | 3 | 2 | Clean Code | 1 | +---------+----------+------------+-----+
What this code does
- Deleting order 1 also deletes its two items, because the foreign key says
ON DELETE CASCADE. - Order 2 and its item are untouched: only children of the deleted parent go.
- Here CASCADE is right: an order item without its order cannot be shipped and means nothing.
Example 4 · The cascade chain that wipes out sales history
CREATE TABLE shop_customers ( customer_id INT PRIMARY KEY, name VARCHAR(40) NOT NULL);CREATE TABLE shop_orders ( order_id INT PRIMARY KEY, customer_id INT NOT NULL, FOREIGN KEY (customer_id) REFERENCES shop_customers (customer_id) ON DELETE CASCADE);CREATE TABLE shop_order_items ( order_id INT NOT NULL, product VARCHAR(40) NOT NULL, amount INT NOT NULL, PRIMARY KEY (order_id, product), FOREIGN KEY (order_id) REFERENCES shop_orders (order_id) ON DELETE CASCADE);
INSERT INTO shop_customers VALUES (1, 'Asha'), (2, 'Ravi');INSERT INTO shop_orders VALUES (101, 1), (102, 1), (103, 2);INSERT INTO shop_order_items VALUES (101, 'Redmi Note 13', 17999), (101, 'boAt Airdopes', 2598), (102, 'Lenovo IdeaPad', 42990), (103, 'Clean Code', 499);
-- Asha closes her accountDELETE FROM shop_customers WHERE customer_id = 1;
SELECT (SELECT COUNT(*) FROM shop_customers) AS customers, (SELECT COUNT(*) FROM shop_orders) AS orders, (SELECT COUNT(*) FROM shop_order_items) AS order_items;+-----------+--------+-------------+ | customers | orders | order_items | +-----------+--------+-------------+ | 1 | 1 | 1 | +-----------+--------+-------------+
What this code does
- One DELETE on one customer removed 2 orders and 3 order items as well: the cascade ran from customers to orders, then from orders to items.
- Asha's Rs. 63,587 of sales are gone from every report, and nothing warned you. This is why
orders→customersshould be RESTRICT. - The last query counts each table with small sub-queries in brackets; the Subqueries module explains that form.
Typical cases are the items of an order, the marks of a student, the berths of a cancelled booking.
The risk is that one small DELETE can remove far more than you meant. Cascades follow chains: if orders cascade from customers and order_items cascade from orders, deleting one customer silently deletes their whole sales history. The client's "1 row affected" counts only the parent row, not the children it took with it. ON UPDATE CASCADE is the partner rule for key changes: if a parent's key value changes, the children's foreign key values change with it. With surrogate keys that never change it is rarely needed; with natural keys such as a station code it saves updating every child by hand.
SET NULL
ON DELETE SET NULL keeps the child rows and sets their foreign key column to NULL when the parent row is deleted. The column must allow NULL.
A product whose category is removed can still be sold. The snippet shows the rule, and Example 5 deletes a category.
CREATE TABLE support_agents ( agent_id INT PRIMARY KEY);
CREATE TABLE support_tickets ( ticket_id INT PRIMARY KEY, agent_id INT NOT NULL, -- cannot become NULL CONSTRAINT fk_support_tickets_agent FOREIGN KEY (agent_id) REFERENCES support_agents (agent_id) ON DELETE SET NULL);ERROR 1830 (HY000): Column 'agent_id' cannot be NOT NULL: needed in a foreign key constraint 'fk_support_tickets_agent' SET NULL
Example 5 · SET NULL: a category is removed
CREATE TABLE store_categories ( category_id INT PRIMARY KEY, name VARCHAR(30) NOT NULL);
CREATE TABLE store_products ( product_id INT PRIMARY KEY, name VARCHAR(40) NOT NULL, category_id INT NULL, CONSTRAINT fk_store_products_category FOREIGN KEY (category_id) REFERENCES store_categories (category_id) ON DELETE SET NULL);
INSERT INTO store_categories VALUES (6, 'Fashion'), (7, 'Toys');INSERT INTO store_products VALUES (12, 'Cotton Kurta', 6), (13, 'Running Shoes', 6), (14, 'Rubik''s Cube', 7);
DELETE FROM store_categories WHERE category_id = 6;
SELECT * FROM store_products ORDER BY product_id;+------------+---------------+-------------+ | product_id | name | category_id | +------------+---------------+-------------+ | 12 | Cotton Kurta | NULL | | 13 | Running Shoes | NULL | | 14 | Rubik's Cube | 7 | +------------+---------------+-------------+
What this code does
- The Fashion category is deleted, but its two products stay; only their
category_idbecomes NULL. - The Toys product is untouched. The shop can still sell the kurta and the shoes and give them a new category later.
Use SET NULL when the child still makes sense without the parent, like a ticket whose assigned support agent leaves and still needs an answer. The NULL says "not linked any more", just as the gift card in the practice data has no category. If the column is NOT NULL, MySQL refuses to create the rule, as this snippet shows.
Choosing the rule for orders and customers
A soft delete marks a row as inactive, with a column such as is_active, instead of removing it. The app hides the row, and every link and history record stays intact.
Example 6 gives every link of the ShopLite schema the rule that fits what its rows mean.
Example 6 · ShopLite with a rule for every link
CREATE TABLE sl_categories (category_id INT PRIMARY KEY, name VARCHAR(30) NOT NULL);CREATE TABLE sl_customers ( customer_id INT PRIMARY KEY, name VARCHAR(40) NOT NULL, is_active BOOLEAN NOT NULL DEFAULT TRUE -- for soft delete);CREATE TABLE sl_products ( product_id INT PRIMARY KEY, category_id INT NULL, CONSTRAINT fk_sl_products_category FOREIGN KEY (category_id) REFERENCES sl_categories (category_id) ON DELETE SET NULL);CREATE TABLE sl_orders ( order_id INT PRIMARY KEY, customer_id INT NOT NULL, CONSTRAINT fk_sl_orders_customer FOREIGN KEY (customer_id) REFERENCES sl_customers (customer_id) ON DELETE RESTRICT);CREATE TABLE sl_order_items ( order_id INT NOT NULL, product_id INT NOT NULL, PRIMARY KEY (order_id, product_id), CONSTRAINT fk_sl_order_items_order FOREIGN KEY (order_id) REFERENCES sl_orders (order_id) ON DELETE CASCADE, CONSTRAINT fk_sl_order_items_product FOREIGN KEY (product_id) REFERENCES sl_products (product_id) ON DELETE RESTRICT);
SELECT constraint_name, delete_ruleFROM information_schema.referential_constraintsWHERE constraint_schema = DATABASE() AND constraint_name LIKE 'fk_sl_%'ORDER BY constraint_name;+---------------------------+-------------+ | CONSTRAINT_NAME | DELETE_RULE | +---------------------------+-------------+ | fk_sl_order_items_order | CASCADE | | fk_sl_order_items_product | RESTRICT | | fk_sl_orders_customer | RESTRICT | | fk_sl_products_category | SET NULL | +---------------------------+-------------+
What this code does
- Each foreign key gets the rule from the list above: SET NULL for categories, RESTRICT for customers and products, CASCADE for order items.
- Writing
ON DELETE RESTRICTinstead of leaving the rule out behaves the same, but it tells the next reader the choice was made on purpose. - Closing an account becomes
UPDATE sl_customers SET is_active = FALSE WHERE customer_id = ...;: the orders keep their customer.
Choose each rule by asking: if the parent goes, should the child go, stay unlinked, or stop the delete? For ShopLite that gives:
orders→customers: RESTRICT. Orders are money and tax records; never lose them because an account closed.order_items→orders: CASCADE. Items are part of their order; an order deleted by an admin takes its lines with it.products→categories: SET NULL. The product stays; only its category link is cleared.order_items→products: RESTRICT. Old orders must keep pointing at the product they sold.
Real shops rarely delete customers or products at all. They use a soft delete instead, and RESTRICT then guards against a hard delete by mistake.
Next step
Check your understanding
11 quick questions