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?
CodePudding user response:
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.