Skip to content
Advertisement

MYSQL – Cannot update a parent row: a foreign key constraint fails

db-fiddle link

SCHEMA QUERY

UPDATE QUERY

ERROR

If I change the ID of FIELD_TEST,

To change the GRAPH_FIELD_INFO_TEST ID referring to the ID of FIELD_TEST,

I set it as cascade in GRAPH_FIELD_INFO_TEST.

However, it fails due to the following error.

If I change the ID of GRAPH, it works normally,

but it doesn’t work properly only if the ID of FIELD_TEST is changed.

May I know the reason for the error?

Advertisement

Answer

The field_id in your FIELD_TEST table is a CHAR(50), but in your GRAPH_FIELD_INFO_TEST table it’s a CHAR(32).

CHAR is a fixed length type, so when you update the field_id in the FIELD_TEST table the system tries also to update the referencing column with a CHAR(50) which of course fails (eventhough the actual value is only 32 chars). Thus the error. Change the field_id column to have the same length in all tables … As noted in your db-fiddle, it works for the GRAPH... relations, because the fields in all tables have the same length.

Yes, it works when you insert data, because 'foobar' = 'foobar' even when one of them comes from a CHAR(50) column and the other one from a CHAR(32) column. So when you insert in your child table it can find a corresponding key in the parent table. But it can’t do a cascading update when the parent key is longer than the child.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement