Cascade update fails on MySQL for foreign keys referring to the same table

Recently I noticed that ON UPDATE CASCADE rule falis in MySQL on InnoDB tables if a foreign key references the same table, which is usual for tree-like data structured. At the same time ON DELETE CASCADE works fine.

This  is not corresponding to SQL standard but it’s already stated in MySQL manual:

Deviation from SQL standards: If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT

It was also reported as a bug but rejected years ago.

There could be some complex workarounds for this problem but my solution is simply avoiding natural primary keys and using surrogate (auto increment) primary keys instead. Surrogate key values won’t change so you don’t have to care about ON UPDATE rules at all.

Leave a Comment