0

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?

1
  • When showing the schema, use SHOW CREATE TABLE. That will include all indexes, the engine, and all foreign keys. That will help us diagnose the problem.
    – Rick James
    Dec 3 '21 at 17:33
1

The purpose of a Foreign Key is to guarantee that a corresponding values exists in another table.
A simplistic example would be that an OrderLine record cannot exist without a matching OrderHeader record.

select * from OrderHeader ; 

+---------------+------------+
| order_id (pk) | order_date | ... 
+---------------+------------+
|        123456 | 2021/11/03 | 
+---------------+------------+

select * from OrderLine ; 

+---------------+-----------+----------+
| order_id (fk) | stock_num | quantity | ... 
+---------------+-----------+----------+
|        123456 |    234567 |        1 | 
+---------------+-----------+----------+

So, with this Data and a Foreign Key constraint between the two tables on order_id, you cannot change the OrderHeader record's order_id, because that would leave the OrderLine record "orphaned" (even if you intended to update the OrderLine records to match, not that you should ever change record id's, but that's another story).
The Foreign Key actively prevents you from doing this.

You're seeing the same with your Tables & Data.

The Foreign Key from GRAPH_FIELD_INFO_TEST to FIELD_TEST (on field) is preventing you from changing the field column in FIELD_TEST for exactly this reason.

To make this change, you must insert a new record into FIELD_TEST with the new Primary Key value, update the Foreign Key values in GRAPH_FIELD_INFO_TEST to match this new value and then delete [the record with] the old primary key from FIELD_TEST, something like this:

insert into FIELD_TEST 
select (new_id), ... 
from FIELD_TEST 
where field = (old_id) ; 

update GRAPH_FIELD_INFO_TEST 
set field = (new_id) 
where field = (old_id) ; 

delete from FIELD_TEST 
where field = (old_id) ; 
-1

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).

0

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.