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

db-fiddle link

SCHEMA QUERY

-- GRAPH INFO TABLE--------------------------------------------------------------

CREATE TABLE GRAPH
(
    graph_id         CHAR(32)       NOT NULL   PRIMARY KEY,
    name             VARCHAR(1024)  NOT NULL
);

INSERT INTO GRAPH set graph_id = MD5('graph1'),
                      name = 'graph1';
                               
INSERT INTO GRAPH set graph_id = MD5('graph2'),
                      name = 'graph2';
                      
INSERT INTO GRAPH set graph_id = MD5('graph3'),
                      name = 'graph3';
                      
-- FIELD INFO TABLE--------------------------------------------------------------

CREATE TABLE FIELD_TEST
(
    field_id    CHAR(50)        NOT NULL   PRIMARY KEY,
    name        VARCHAR(500)    NOT NULL
);

INSERT INTO FIELD_TEST set field_id = MD5('field1'),
                           name = 'field1';
                               
INSERT INTO FIELD_TEST set field_id = MD5('field2'),
                           name = 'field2';
                      
INSERT INTO FIELD_TEST set field_id = MD5('field3'),
                           name = 'field3';
                           
-- GRAPH FIELD RELATION TABLE-----------------------------------------------------

CREATE TABLE GRAPH_FIELD_INFO_TEST
(
    field_id    CHAR(32)                                   NOT NULL,
    graph_id    CHAR(32)                                   NOT NULL,
    PRIMARY KEY (graph_id, field_id),
    CONSTRAINT GRAPH_FIELD_INFO_TEST_FIELD_field_id_fk
        FOREIGN KEY (field_id) REFERENCES FIELD_TEST (field_id)
            ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT GRAPH_FIELD_INFO_TEST_GRAPH_STORAGE_graph_id_fk
        FOREIGN KEY (graph_id) REFERENCES GRAPH (graph_id)
            ON UPDATE CASCADE ON DELETE CASCADE
);

INSERT INTO GRAPH_FIELD_INFO_TEST set field_id = MD5('field1'),
                                      graph_id = MD5('graph1');
                                      
INSERT INTO GRAPH_FIELD_INFO_TEST set field_id = MD5('field2'),
                                      graph_id = MD5('graph2');     
                                      
INSERT INTO GRAPH_FIELD_INFO_TEST set field_id = MD5('field3'),
                                      graph_id = MD5('graph3');                                      

UPDATE QUERY

UPDATE FIELD_TEST
SET name     = 'new field1',
    field_id = MD5('new field1')
WHERE field_id = MD5('field1');

ERROR

Query Error: Error: ER_ROW_IS_REFERENCED_2: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`GRAPH_FIELD_INFO_TEST`, CONSTRAINT `GRAPH_FIELD_INFO_TEST_FIELD_field_id_fk` FOREIGN KEY (`field_id`) REFERENCES `FIELD_TEST` (`field_id`) ON DELETE CASCADE ON UPDATE CASCADE)

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?

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.