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 CASCADEor
ON UPDATE SET NULLrecurses to update the same table it has previously updated during the cascade, it acts like
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.