2015年1月8日 星期四

[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 處理,如此一來刪除資料就輕鬆許多,不必做多個處理。

1 則留言:

  1. I recommend this tutorial
    http://agiledata.org/essays/dataNormalization.html.

    回覆刪除