I have two databases created using the same script, once on the server, and once on my local machine.
On both, I try to add a row in a table, with one column referencing a foreign key of another table for which the referenced ID does not exist yet.
On my local machine, I get this error: ERROR 1452: 1452: Cannot add or update a child row: a foreign key constraint fails [info on which foreign key violation here]
On the server, I get this error: ERROR 1216: 1216: Cannot add or update a child row: a foreign key constraint fails
Note the different error codes, and also the server does not specify which foreign key constraint is violated.
My local MySQL version: 8.0.27 (Windows machine)
Server MySQL version: 8.0.32-0ubuntu0.22.04.2
What could be causing the difference?
I would like my server to tell me which foreign key constraint is being violated so I can debug errors. Am I missing a config?
(Both queries were done using MySQL Workbench 8.0CE, also using .Net Core EntityFramework for MySQL with same results)
As per mysql manual on foreign keys:
If a user has table-level privileges for all parent tables, ER_NO_REFERENCED_ROW_2 and ER_ROW_IS_REFERENCED_2 error messages for foreign key operations expose information about parent tables. If a user does not have table-level privileges for all parent tables, more generic error messages are displayed instead (ER_NO_REFERENCED_ROW and ER_ROW_IS_REFERENCED).
ER_NO_REFERENCED_ROW maps to 1216, while ER_NO_REFERENCED_ROW_2 maps to 1452 error messages. So, you need to have access to all affected tables in order to get 1452 error message. This makes sense from a security point of view.