Mysql

MYSQL - 無法更新父行:外鍵約束失敗

  • December 3, 2021

db小提琴連結

模式查詢

-- 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 FIELD_TEST
SET name     = 'new field1',
   field_id = MD5('new field1')
WHERE field_id = MD5('field1');

錯誤

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)

如果我更改 FIELD_TEST 的 ID,

參考 FIELD_TEST 的 ID 更改 GRAPH_FIELD_INFO_TEST ID,

我在 GRAPH_FIELD_INFO_TEST 中將其設置為級聯。

但是,由於以下錯誤,它失敗了。

如果我更改 GRAPH 的 ID,它可以正常工作,

但只有更改 FIELD_TEST 的 ID 時,它才能正常工作。

我可以知道錯誤的原因嗎?

外鍵的目的是保證對應的值存在於另一個表中。

一個簡單的範例是,如果沒有匹配的 OrderHeader 記錄,OrderLine 記錄就不能存在。

select * from OrderHeader ; 

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

select * from OrderLine ; 

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

因此,使用 order_id 上的兩個表之間的此數據和外鍵約束,您無法更改 OrderHeader 記錄的 order_id,因為這會使 OrderLine 記錄“孤立”(即使您打算更新 OrderLine 記錄以匹配,而不是你應該改變記錄ID,但那是另一回事)。

外鍵主動阻止您這樣做。

您的表格和數據也看到了同樣的情況。

正是出於這個原因,從 GRAPH_FIELD_INFO_TEST 到 FIELD_TEST(在欄位上)的外鍵阻止您更改 FIELD_TEST 中的欄位列。

要進行此更改,您必須使用新的主鍵值將新記錄插入 FIELD_TEST,更新**GRAPH_FIELD_INFO_TEST中的外鍵值以匹配此新值,然後刪除

$$ the record with $$FIELD_TEST 中的舊主鍵,如下所示:

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

FIELD_TEST 表中的 field_id 是 CHAR(50),但在 GRAPH_FIELD_INFO_TEST 表中它是 CHAR(32)。

引用自:https://dba.stackexchange.com/questions/303451