2

Am I misunderstanding something here? I have two tables, table1 and table2. Table2 has a foreign key that references a field in table1 with ON DELETE SET NULL associated with the foreign key. Table2 also has a TIMESTAMP field that has ON UPDATE CURRENT_TIMESTAMP set.

Modifying an entry in table2 successfully updates the timestamp field. Deleting the linked entry in table1 successfully sets the foreign key field in table2 to NULL, but does not update the TIMESTAMP field in table2.

When the field in in table2 gets set to NULL, isn't that considered an UPDATE, and the timestamp should get updated too?

  • FKs are not do-all. It may be better to write your own code and wrap it in a transaction. – Rick James Feb 25 '18 at 20:39
3

This is documented, although not very clearly, in MySQL docs: InnoDB restrictions:

  • Cascaded foreign key actions do not activate triggers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.