12-2
驗證Cascade
功能
- AppServ
內容:
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 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 表內相關紀錄是否也跟隨著被更改。
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
正確 |
|