與FOREGIN KEY的久違重逢，發生了點意外...

2023-11-10

問題1. Error Code: 1005

最近還是在開發權限管理系統，磕磕碰碰終於可以開始搞CRUD了，做到刪除功能時我突然想起之前做Rails專案的時候有FOREGIN KEY，可以確保某些資料的同步，於是開始我的各表之間關聯性連結~
(為甚麼一開始設計的時候沒想到?因為我連DB怎麼連都不知道，更別說像之前直接輸入rails g migration 直接生表出來，東拼西湊才弄出一點orz)

我有兩個table:

父表- user:用於儲存使用者資訊
子表- pages_authorize:用於儲存頁面授權清單，其中一個欄位Id要對照user，因為我需要在刪除user表的資料時，一併刪除相關的資料，所以要利用外部鍵建立關聯

ALTER TABLE sys.pages_authorize
ADD CONSTRAINT fk_pageAuth_user
FOREIGN KEY (Id)
REFERENCES sys.user(Id);

當我要執行插入外部鍵的動作，發生錯誤訊息

1	Error Code: 1005. Can't create table 'sys.#sql-540_1e' (errno: 150)

問題檢查

1. 檢查欄位的資料類型、大小、編碼是否相同

會發生問題的狀況如下:

pages_authorize的Id欄為varchar(10) ，user的Id欄為char(10)
pages_authorize的Id欄為char(10) ，user的Id欄為char(20)
pages_authorize的Id欄為 utf8mb4_general_ci編碼，user的Id欄為utf8mb4_unicode_ci編碼

2. 父表欄位必須是唯一鍵或主鍵

如果user表被對照的欄位Id不是主鍵或唯一，會導致參照問題(你叫我去捷運站的小七取貨，結果有兩家小七，不知道要去哪一間的感覺)。

3. 確保父表已經被創建

這就要看user是否已經建立，爸爸都還沒出生，小孩怎麼出生呢~

4. 引用的表格和欄位都存在並且沒有拼寫錯誤

就是拼字問題…請檢查有沒有打錯名稱，就像小七跟小匕不一樣

5. 確認是否為相同的資料庫引擎(本次有發生的問題)

我的user資料是之前從別的地方複製過來的，引擎是MyISAM，而pages_authorize的引擎是InnoDB，除了引擎不同以外，MyISAM不支援外部鍵的功能

6. 確認該外部鍵名是否重複

如果有兩個相同命名的fk會無法成功建立。

解決方式

輸入指令把user的引擎改成InnoDB就能成功執行了

1	ALTER TABLE sys.user ENGINE=InnoDB;

因為這是我自己在玩的專案，隨便改沒有關係，但如果是正在運行或是重要的專案還是要謹慎操作，至少要先備份以免資料發生異常，不過一般資料庫最好不要亂動比較好，特別是這種基礎設定

問題2. Error Code: 1452

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`sys`.`#sql-540_1e`, CONSTRAINT `fk_pageCname_sysCname` FOREIGN KEY (`SysCode`) REFERENCES `system_cname` (`System`))

如果外部鍵是後來才加的，就有可能遇到這個問題(就像我)，這個問題大致上可以說是資料格式不符合不能加外部鍵

問題檢查

同上，檢查欄位的資料類型、大小、編碼是否相同
檢查子表要連結的欄位是否都包含在父表欄位中(本次問題)

解決方式

方法一、

我的父表為system_cname，欄位有System(系統代碼)、CName(對照中文)
子表為page_cname，欄位有SysCode(對應系統代碼)、PageCode(頁面代碼)、CName(對照頁面中文)
一個系統有很多頁面，所以父表資料對子表資料為一對多的關係，以父表的System和子表的SysCode用外部鍵連結。
一一核對之後發現子表的SysCode有一筆在父表的System上沒有對應的資料，在添加對應的系統代碼之後就可正常運行

方法二、禁用外部鍵檢查

1	SET foreign_key_checks = 0

有這功能但盡量不要使用，畢竟我們使用外部鍵就是為了確保資料的一致性，不檢查就有可能出現漏網之魚，非萬不得已不要使用。

目前有使用到的情境是DEBUG的時候:

1	Error Code: 1146. Table 'XXX' doesn't exist

打開MySQL Workbench，資料表明明也還在，但是要點開的時候依舊顯示這個錯誤訊息，由於這個問題是在新增外部鍵之後發生的，那就有可能是外部鍵有問題，把檢查關閉，進行偵錯，發現關掉就能正常運行，至於怎麼排除錯誤就不多說了~
因為是不小心一個操作失誤直接導致table被刪掉…重創table的時候直接加入FK就正常了，但這絕對不會是一個好的解決方式，這告訴我們資料關聯在剛開始寫專案的時候就要想好，後來才插入容易出問題的，無論是改資料格式、類型、大小，都有不小心把table搞不見的風險。

參考資料

跟GPT問出來的
13.1.20.5 FOREIGN KEY Constraints
重启MySQL服务后，导致数据表不存在的问题记录

問題1. Error Code: 1005

問題檢查

1. 檢查欄位的資料類型、大小、編碼是否相同

2. 父表欄位必須是唯一鍵或主鍵

3. 確保父表已經被創建

4. 引用的表格和欄位都存在並且沒有拼寫錯誤

5. 確認是否為相同的資料庫引擎(本次有發生的問題)

6. 確認該外部鍵名是否重複

解決方式

問題2. Error Code: 1452

問題檢查

解決方式

方法一、

方法二、 禁用外部鍵檢查

參考資料

方法二、禁用外部鍵檢查