[MySQL] 使用 FOREIGN KEY 筆記 @ MySQL 5.6

話說，2014年算是我最常用 SQL DB 的一年，在這之前我都是用 NOSQL 架構 Orz (扣除 SQLite 啦)。最近設計一些新服務的資料儲存，正在想如何使用 MySQL Relations 的特色，才發現之前都沒在用 FOREIGN KEY 啦 :P

簡短介紹 FOREIGN KEY 的功用：

當設計很多階層性的 table 時，如 user (上層), group (中層), data (下層)，其中 data 每一筆都有 user, group 資訊，而 group 裡每一筆都跟 user 有關。此時在 DELETE 事件發生時，如果有建立 Relations 時，可在上層資料刪除時，順便幫你把相關資料刪掉。此外，在新增下層資料時，也會幫你驗證資料的正確性，不會讓你隨意新增假資料。

對於要建立 FOREIGN KEY 的要件：

• 在上層(Parent)預計使用的參考欄位要有 index 屬性，如 Primary Key, Unique Key 或 Index 都行
• 在下層(Child)預計使用的欄位也要有 index 屬性
• 在上層跟下層對應的欄位型態要一樣，例如一樣為非負整數等

此外，則是可以設定 Action，例如上層刪除、更新資料時，下層是否也要一同更新，其選項：

• Restrict: 拒絕 parent event，也將造成 parent 動作失敗
• Cascade: 跟著 parent event 更新或是刪除
• Set null: 收到 parent event 時，將參考欄位設定成 null
• No action: 標準 SQL 語法且為預設選項，在 MySQL 環境上與 Restrict 等價

下次是操作時碰到的問題：

Q: Cannot add foreign key constraint
A: 仔細確認一下指定的欄位，其型態是不是一致的，或是對應的 Action 若為 Set Null 時，要確認資料欄位是否允許 Null

Q: Cannot add or update a child row: a foreign key constraint fails
A: 追蹤一下已存在的資料，是不是有不合理的地方，例如下層存在一筆資料，其對應上層的資料無法匹配。解法就是手動修正，或是乾脆清光資料來做也行。

整體上，建議有參考關係時，可以面對 Parent delete event 時，可以設定成 Cascade 處理，如此一來刪除資料就輕鬆許多，不必做多個處理。