資料庫程式設計 – 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 建立外來鍵Cascadecourse_db

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

1.        Not Action:吾人設計 dep_IDclass_ID 不允許更新或刪除,如果允許變更的話,整個資料庫影響非常大。如真的需要刪除或更新時,則須由參考欄位變更後,再變更被參考欄位。因此,所有參考到 dep_IDclass_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_IDDep_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 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_IDteacher_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_IDopen_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  表內相關紀錄是否也跟隨著被更改。

n   首先查詢『電腦動畫設計』的 course_ID 為何( all_course ),再查詢該課程是否已開課 ( open_course )

select *

from open_course

where course_ID = (select course_ID

                 from all_course

                 where course_name = "電腦動畫設計");

 

n   再由 all_course 表將『電腦動畫設計』的 course_ID  改為 90A903,完成後再觀察 all_course open_course 表內該紀錄的 course_ID 皆隨之更改 (Update Cascade),如下:

// 更新 course_ID

update all_course

set course_ID = “90A903”

where course_name = “電腦動畫設計”;

// 觀察 open_course 表內 course_ID 是否跟隨更新

select *

from open_course

where course_ID = (select course_ID

                 from all_course

                 where course_name = "電腦動畫設計");

 

Open_course 表內 course_ID 被變更,Update Cascade 正確

翻轉工作室:粘添壽

course_db 關聯圖

 

資料庫程式設計 - PHP+MySQL:

 

 

 

翻轉電子書系列: