/
Cascading changes are used as a way to ensure referential integrity between related tables in MySQL databases. Referential integrity refers to the fact that all references in a database are valid.
Consider the following table:
CREATE TABLE parent (
id INT PRIMARY KEY
);
CREATE TABLE child (
id INT PRIMARY KEY,
parent_id INT,
FOREIGN KEY (parent_id) REFERENCES parent (id)
);
INSERT INTO parent (id) VALUES (1);
Which would create tables that look like this:
In the child table, we have rows that are referencing the parent table (id = 1). As this is set up with a foreign key constraint, we cannot delete the parent row with id = 1 without first deleting the child rows that reference it.
However, with cascading deletes, we can delete the parent row and have the child rows automatically deleted.
You can use cascading changes to automatically apply certain actions to child tables based on what has occurred on the parent table. In this blog, we'll do a deep dive of cascading changes and reasons why you shouldn't use them.
Cascading updates will detect primary key updates and automatically update references in the child table. To enable this, we would do something like this:
CREATE TABLE child (
id INT PRIMARY KEY,
parent_id INT,
FOREIGN KEY (parent_id) REFERENCES
parent(id) ON UPDATE CASCADE
);
If I then update the primary key of the parent, all the child references will be automatically updated.
UPDATE parent SET id = 2 WHERE id = 1;
SELECT * FROM child;
+----+-----------+
| id | parent_id |
+----+-----------+
| 1 | 2 |
| 2 | 2 |
+----+-----------+
In practice, we rarely would use cascading updates as we would try to avoid and minimize the amount of times we would need to change a table's primary keys since it could impact external applications.
Cascading deletes will detect a delete in the parent table and automatically delete all the referenced child rows. To enable this, we would do something like this:
CREATE TABLE child (
id INT PRIMARY KEY,
parent_id INT,
FOREIGN KEY (parent_id) REFERENCES
parent(id) ON DELETE CASCADE
);
With cascading deletes, if I deleted an entry from the parent, the respective child entries will be automatically deleted.
SELECT * FROM child;
+----+-----------+
| id | parent_id |
+----+-----------+
| 1 | 1 |
| 2 | 1 |
+----+-----------+
2 rows in set (0.00 sec)
DELETE FROM parent where id = 1;
Query OK, 1 row affected (0.01 sec)
SELECT * FROM child;
Empty set (0.00 sec)
The key differences are:
There are several important reasons to avoid cascading changes in production environments:
Cascading changes makes the database more susceptible to unintended changes:
For example: If you are an e-commerce company, and you delete a row in the products table and the orders table is set to cascade delete, you will lose all the order history for that product.
Cascading changes are also bad for performance as they require a serializable lock:
Instead of using cascading changes, consider these alternatives:
Handle related record maintenance at the application level, where you have more control and visibility:
// PHP example of handling parent-child deletion with archiving
function deleteParentWithChildren($parentId) {
try {
db.beginTransaction();
// Archive children first
db.execute("INSERT INTO archived_children SELECT * FROM child WHERE parent_id = ?", [parentId]);
// Delete children
db.execute("DELETE FROM child WHERE parent_id = ?", [parentId]);
// Archive parent
db.execute("INSERT INTO archived_parents SELECT * FROM parent WHERE id = ?", [parentId]);
// Delete parent
db.execute("DELETE FROM parent WHERE id = ?", [parentId]);
db.commit();
} catch (Exception $e) {
db.rollback();
throw $e;
}
}
Use logical deletion (soft deletes) instead of physical deletion:
ALTER TABLE parent ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE;
ALTER TABLE child ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE;
-- Instead of DELETE FROM parent
UPDATE parent SET is_deleted = TRUE WHERE id = 1;
UPDATE child SET is_deleted = TRUE WHERE parent_id = 1;
If you need automatic actions, use triggers that include proper logging:
DELIMITER //
CREATE TRIGGER after_parent_delete
AFTER DELETE ON parent
FOR EACH ROW
BEGIN
-- Log the deletion
INSERT INTO deletion_log (table_name, record_id, deleted_at)
VALUES ('parent', OLD.id, NOW());
-- Delete children with logging
INSERT INTO deletion_log (table_name, record_id, parent_id, deleted_at)
SELECT 'child', id, OLD.id, NOW() FROM child WHERE parent_id = OLD.id;
DELETE FROM child WHERE parent_id = OLD.id;
END//
DELIMITER ;
To remove cascading changes, you will need to recreate the constraint. Below is a step-by-step guide:
SELECT CONSTRAINT_NAME FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'child'
AND COLUMN_NAME = 'parent_id';
+-----------------+
| CONSTRAINT_NAME |
+-----------------+
| child_ibfk_1 |
+-----------------+
1 row in set (0.01 sec)
START TRANSACTION;
-- (optional) Lock on the parent and child tables to prevent data changes
LOCK TABLES parent WRITE, child WRITE;
-- Drop the existing foreign key constraint
ALTER TABLE child DROP FOREIGN
KEY child_ibfk_1;
-- Recreate the constraint
ALTER TABLE child ADD CONSTRAINT
child_table_ibfk_1 FOREIGN KEY (parent_id)
REFERENCES parent (id);
-- Unlock the tables (if locked)
UNLOCK TABLES;
COMMIT;
When removing or changing foreign key constraints, keep these points in mind:
While MySQL cascading changes provide a convenient way to maintain referential integrity, they come with significant drawbacks that make them unsuitable for most production environments. The risks of unintended data loss, debugging difficulties, and performance issues generally outweigh the benefits of automatic maintenance.
Instead, consider implementing application-level integrity checks, soft deletes, or well-designed triggers with proper logging. These approaches give you better control, visibility, and recoverability while still maintaining data consistency across your database.
If you already have cascading changes enabled in your database, follow the steps outlined above to safely remove them and implement a more robust alternative.
