Mysql
MYSQL - 無法更新父行:外鍵約束失敗
模式查詢
-- 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)。