MySQL Forums
Forum List  »  General

Re: On update doesn't work
Posted by: Peter Brawley
Date: September 08, 2021 08:46AM

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.

Options: ReplyQuote

Written By
September 07, 2021 01:23PM
September 07, 2021 02:49PM
September 08, 2021 03:18AM
September 08, 2021 03:29AM
Re: On update doesn't work
September 08, 2021 08:46AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.