Re: On update doesn't work

[Peter Brawley]

That's an InnoDB ISO SQL non-compliance issue, it doesn't support DEFERRED FK updates ...

"In an SQL statement that inserts, deletes, or updates many rows, foreign key constraints (like unique constraints) are checked row-by-row. When performing foreign key checks, InnoDB sets shared row-level locks on child or parent records that it must examine. MySQL checks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. This means that it is not possible to delete a row that refers to itself using a foreign key."

The awkward workaround is to write a transaction that saves off the FK values, updates the PK, then updates the FK values.