MySQL raises ER_FK_DEPTH_EXCEEDED when a cascade DELETE or UPDATE would traverse more than the server’s maximum allowed foreign-key depth.
ER_FK_DEPTH_EXCEEDED occurs when a cascading foreign-key action exceeds MySQL’s max depth (default 15). Reduce the chain length or disable cascading to fix the issue.
ER_FK_DEPTH_EXCEEDED
MySQL throws ER_FK_DEPTH_EXCEEDED (SQLSTATE HY000) when it detects that a cascade DELETE or UPDATE would follow more parent-child relationships than the configured maximum depth. The default depth is 15 levels as of MySQL 5.7.2.
The error appears at execution time, not during table creation. It surfaces while MySQL evaluates the cascaded action and realises that continuing would breach the depth quota.
Ignoring the error leaves orphaned rows or blocks critical maintenance jobs. Clearing the limitation keeps referential integrity intact and prevents partial data modifications.
A long chain of foreign keys with ON DELETE CASCADE or ON UPDATE CASCADE can easily surpass the limit in highly normalised schemas.
Recursive self-referencing tables that model hierarchical data can trigger the error if they rely on cascading updates.
Generated code that adds automatic cascade clauses without depth awareness commonly causes unexpected failures.
Measure the depth of your foreign-key chain. Remove unnecessary ON DELETE/UPDATE CASCADE clauses or break the chain by handling changes in application code.
Temporarily disable cascading, perform the mutation manually, and re-enable constraints for rare maintenance operations.
-- example: convert deepest table to SET NULL instead of CASCADE
ALTER TABLE orders_items
DROP FOREIGN KEY fk_order_item_order,
ADD CONSTRAINT fk_order_item_order
FOREIGN KEY (order_id)
REFERENCES orders(id)
ON DELETE SET NULL;
Large ERP databases often link customers -> orders -> shipments -> invoices. Changing the shipment table to SET NULL rather than CASCADE usually stays within depth limits.
Nested category trees modelled with self-joins benefit from switching to a stored procedure that walks the tree instead of relying on declarative cascades.
Limit cascade usage to truly dependent tables. Prefer SET NULL or NO ACTION for less critical links.
Add integration tests that insert sample data across full depth chains and run DELETE to catch depth issues early.
ER_ROW_IS_REFERENCED_2 signals blocked deletes due to NO ACTION foreign keys and is fixed by deleting child rows first.
ER_NO_REFERENCED_ROW_2 means an insert lacks a matching parent; ensure the parent record exists or drop the constraint.
More than 15 parent-child hops in a single cascade action exceeds the default depth and raises the error.
Hierarchical tables using ON DELETE CASCADE can loop through generations and surpass the depth limit.
Code generators that blanket-apply CASCADE options create deep chains unintentionally.
Occurs when a parent row is deleted while children exist without CASCADE.
Raised when inserting a child row without a matching parent.
Signals mismatched column definitions between parent and child foreign keys.
No. The depth is hard-coded at 15 in MySQL 5.7 and later. Redesign the schema instead.
No. Only CASCADE actions count toward the depth limit.
No. All InnoDB tables share the same cascade depth rule.
Galaxy’s editor surfaces foreign-key graphs and warns when chains approach risky depth, letting you refactor before deployment.
.avif)
.avif){kind=logo}
