級聯 SQL Cascade 簡介

資料庫程式設計 – PHP+MySQL ：第十二章外來鍵 Cascade 下一頁

第十二章外來鍵 Cascade

12-1 級聯 Cascade簡介

內容：

12-1-1 外來鍵 Cascade 功能

12-1-2 InnoDB 與 Cascade 語法

12-1-3 刪除外來鍵 - AppServ

12-1-1 外來鍵 Cascade 功能

『外來鍵』(Foreign Key) 表示資料表的某一欄位參考到另一資料表的某欄位，又被參考的欄位大多是『主鍵』(Primary Key)。亦是，外來鍵是跟隨另一資料表的主鍵而生，當被參考的主鍵內的『值』被刪除或更新時，外來鍵相對的該『值』的紀錄，是否隨著被刪除或更新，這就是『Cascade』的問題。但發生 Cascade 狀況並非僅兩個資料表之間，也許會發生在多個資料表之間。吾人以『course_db』部分資料表之間的關聯為例，由下圖可以看出，它們之間的關聯是：

Student_ID(slect_course) =>student_ID(students)、class_ID(students) => class_ID(classes)、dep_ID(classes) => dep_ID(department)

如表格內某欄位是屬於『外來鍵』的話，則表示其內容是隨著被參考欄位內容而改變，而改變情況有：更新、刪除動作，但表格之間可能連續幾個表格之間牽連變更，因此稱之為『級聯』(Cascade)，由上圖可能出現狀況說明如下：

(A) 更新動作

n 當某系所的 dep_ID 變更(department) 時，classes 表內的 dep_ID 是否必須隨著更新，如沒有連帶變更的話，以後就查不出來該班是屬於哪一系所。

n 當某一班級代碼 class_ID 變更 (classes) 時，students 表內的 class_ID 也必須隨之更新，否則查不到該生屬於哪一班級。

(B) 刪除動作

刪除動作可複雜了許多，範例說明如下：

n 當某位學生休退學由 students 表內刪除其 student_ID，則相對應的 select_course 表內有該學生的 student_ID 也需刪除 (沒有選課了)。

n 假如某一系 (如資訊工程) 被刪除時 (由 department 的 dep_ID)，則屬於該系的班級 (classes) 是否也需刪除，當該系的班級 class_ID 被刪除時，則該班的學生是否也須隨之被刪除 (students 表內的 student_ID)，跟隨著被刪除的學生選課是否也需被刪除 (select_course 表內的 student_ID)，此為『級聯』(Cascade) 的特質。

12-1-2 InnoDB 與Cascade 語法

MySQL 資料庫的儲存引擎有兩種型態：MyISAM 與 InnoDB 兩種型態，前者是早期版本，目前較少用。對於多人使用、安全性要求較高的環境大多使用 InnoDB 型態，才具有 Cascade 之功能，目前在 MySQL 建立表格時，預設型態都指定 InnoDB 型態(功能比較請自行參考其他資料)，如下圖所示

宣告 Cascade 語法如下：(在 Foreign Key 語句增加)

Create Table Table_name (

欄位宣告,

....,

Foreign Key .. References .. On Delete {Cascade | Set Null | Not Action| Restrict}

Foreign Key .. References .. On Update {Cascade | Set Null | Not Action| Restrict}

) Engine InnoDB;

選項敘述如下：

Ø On Delete 或 On Update：當發生刪除或更新動作時，依後面宣告處理。

Ø Cascade：所有關聯的紀錄也會跟隨刪除或更新。

Ø Set Null：將所有關聯的紀錄設定成 Null。(對應欄位不可以 Not Null)

Ø No Action：即是不處理。(預設值)(系統會禁止刪除或更新動作)

Ø Restrict：與 No Action 相同。

12-1-3 刪除外來鍵 - AppServ

如果資料庫中表格的外來鍵沒有規劃 Cascade 功能，或設定 cascade 發生錯誤，而需刪除外來鍵重新設定時，它需要特殊動作才行MySQL 對於刪除外來鍵有所限制，須找出所該外來鍵的 "Foreign Key id"，方法是：由桌面 => 開始 => 由選單中找出 AppServ => MySQL Command Line，如下圖所示。(如使用 mariadb 則由終端機進入 MySQL 交談介面操作)