驗證 SQL Cascade 功能

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

12-2 驗證Cascade 功能 - AppServ

內容：

12-2-1 建立外來鍵 cascade：course_db

12-2-2 驗證：not Action

12-2-3 驗證：on Delete Cascade

12-2-4 驗證：on Update Cscade

12-2-1 建立外來鍵Cascade：course_db

吾人依照『course_db』實際需求，規劃外來鍵 Cascade 功能如下：

1. Not Action：吾人設計 dep_ID、class_ID 不允許更新或刪除，如果允許變更的話，整個資料庫影響非常大。如真的需要刪除或更新時，則須由參考欄位變更後，再變更被參考欄位。因此，所有參考到 dep_ID、class_ID 欄位的外來鍵都設定為『預設值，Not Action』。

2. On Delete Cascade：吾人設計 student_ID 與 teacher_ID 允許刪除 (cascade)，但不允許變更。譬如學生休退學或教師離職，則相關選課與開課應該隨之刪除。但變更學生學號與教師代碼沒有實質意義，而禁止更新。

3. On Delete Cascade On Update Cascade：課程代碼 course_ID 與開課代碼open_course_ID 可能會隨需求而變更或刪除，我們允許它並使它有 Cascade 功能。

依照上述規範，吾人規劃外來鍵特性命令如下：(10 條)

/* 第一條規範 (預設值 Not Action) 禁止 class_ID、Dep_ID 變更 */

Alter Table students ADD Foreign key (class_ID) References classes(class_ID);

Alter Table teachers ADD Foreign key (Dep_ID) References department(Dep_ID);

Alter Table classes ADD Foreign key (Dep_ID) References department(Dep_ID);

Alter Table open_course ADD Foreign key (class_ID) References classes(class_ID);

Alter Table all_course ADD Foreign key (Dep_ID) References department(Dep_ID);

/* 第二條規範 (On Delete Cascade) 禁止 student_ID、teacher_ID 變更但允許刪除 cascade */

Alter Table select_course ADD Foreign key (student_ID) References students(student_ID) On Delete Cascade;

Alter Table open_course ADD Foreign key (teacher_ID) References teachers(teacher_ID) On Delete Cascade;

/* 第三條規範 (On Update Cascade On Delete Cascade)允許 course_ID、open_course_ID 刪除、更新且 cascade */

Alter Table open_course ADD Foreign key (course_ID) References all_course(course_ID) On Delete Cascade On Update Cascade;

Alter Table select_course ADD Foreign key (open_course_ID) References open_course(open_course_ID) On Delete Cascade On Update Cascade;

建立所有外來鍵之後，可觀察到各資料表之間的關係圖，如下：(由網站下載 course_db_foreign.sql 是已建立外來鍵的資料庫)

12-2-2 驗證：not Action

假設吾人欲刪除 department 表內『資訊管理系』紀錄，但它的 dep_ID 被參考到 teachers表的 dep_ID 欄位，則它是被禁止刪除的 (Not Action)，如下：

// 查詢 teachers 表內有資料參考到 department 表內的 dep_ID 欄位。

select teacher_ID, name, dep_ID

from teachers

where dep_ID = (select dep_ID

from department

where dep_name = "資訊管理系");

// 驗證department 表內有關『資訊管理』(dep_ID 欄位) 是禁止刪除的。

delete

from department

where dep_name = "資訊管理系";

(無法刪除，表示該筆記錄有被其它表格參考到。 Not Action 功能)

12-2-3 驗證：on Delete Cascade

在students 表的 student_ID 被參考是允許刪除但不允許更新，吾人直接在 AppServ 上驗證其特性，如下：欄位由 Books 表中刪除『紅樓夢』書籍，觀察 Records 是否還有人租用該本書。

n 首先查詢『林名桂』同學的選課紀錄：(僅印出學號與開課代碼)。

select student_ID, open_course_ID

from select_course

where student_ID = (select student_ID

from students

where name = “林名桂”);

n 假設吾人欲變更其學號，觀察是否允許，如下：

update students

set student_ID = “4001199”

where name = “林名桂”;

表示外來鍵禁止更新

n 吾人由 students 刪除『林名桂』同學紀錄，觀察 select_course 表是否還有它的選課紀錄，如下：

//查詢林名桂同學的學號

Select student_ID, name

from students

where name = “林名桂”;

//刪除林名桂同學的紀錄 (students)，再觀察他的選課紀錄是否一併刪除。

delete

from students

where name = “林名桂”;

select student_ID, open_course_ID

from select_course

where student_ID = “40011124 ”;

傳回空值，Delete Cascade 正確

12-2-4 驗證：on Update Cascade

一般外來鍵允許 Update cascade 大多也允許 Delete Cascade，吾人僅驗證 Update Cascade即可。假設吾人欲將 all_course 表內的『電腦動畫設計』的 course_ID 改為 902A903，觀察 open_course 表內相關紀錄是否也跟隨著被更改。