資料庫系統概論第十二章 多表格資料庫設計   上一頁    下一頁

12-3 進階研討 外來鍵 Cascade

內容:

  • 12-3-1 級聯 (Cascade)

  • 12-3-2 InnoDB 與 Cascade 語法

  • 12-3-3 刪除外來鍵設定

  • 12-3-4 驗證外來鍵 cascade 功能

12-3-1 級聯 (Cascade)

『外來鍵』(Foreign Key) 表示資料表某一欄位參考到另一資料表的某一欄位,但該欄位大多是被參考資料表的『主鍵』(Primary Key)。亦是,外來鍵是跟隨另一資料表的主鍵而生,當被參考的主鍵內的『值』被刪除或更新時,外來鍵相對的該『值』,是否隨著被刪除或更新,這就是Cascade』的問題。但發生 Cascade 狀況並非僅兩個資料表之間,也許會發生在多個資料表之間。吾人以『Temple_Library』資料庫為例,下圖是它的 E-R 關聯圖,從中可以看出,Records Book_ID 是主鍵也是外來鍵,它參考到 Books Book_ID 欄位,又 Books Pub_ID 欄位是拽來鍵,參考到 Publishers 表的 Pub_ID 欄位。

Pub_ID (Publishers) -> Pub_ID (Books) -> Book_ID (Books) -> Book_ID (Records)

publishers 表中的『翻轉工作室』出版社被更新或刪除時,是否 Books 表中有『翻轉工作室』的 Pub_ID 的紀錄是否要隨著被更新或刪除,又當 Books 表的相關紀錄被更新或刪除時,Records 的相關紀錄是否又隨之更改,因此稱之為『級聯(Cascade)

12-3-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-3-3 刪除外來鍵設定

原來 temp_library 資料庫中表格並沒有規劃 Cascade 功能,吾人需要將原來設定的外來鍵刪除後,再重新建立。MySQL 對於刪除外來鍵有所限制,須找出所該外來鍵的 "Foreign Key id",方法是:由桌面 => 開始 => 由選單中找出 AppServ => MySQL Command Line,如下圖所示。

    

進入交談式視窗後,輸入密碼 (12345678),接著如下操作,則能查出 Books Records 表外來鍵的識別碼。

Enter password: ********

Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> use temp_library;

Database changed

mysql> show create table books;

....

| books | CREATE TABLE `books` (

  `book_ID` int(11) NOT NULL AUTO_INCREMENT,

  `title` char(50) NOT NULL,

  `author` char(20) NOT NULL,

  `Pub_ID` int(11) NOT NULL,

  `price` int(11) DEFAULT NULL,

  `place` char(20) DEFAULT NULL,

  `remark` char(50) DEFAULT NULL,

  PRIMARY KEY (`book_ID`),

  KEY `Pub_ID` (`Pub_ID`),

  CONSTRAINT `books_ibfk_1` FOREIGN KEY (`Pub_ID`) REFERENCES `publishers` (`pub_ID`)

) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

.....

mysql> show create table records;

.....

records | CREATE TABLE `records` (

  `Guest_ID` char(20) NOT NULL,

  `Book_ID` int(11) NOT NULL,

  `date` date DEFAULT NULL,

  PRIMARY KEY (`Guest_ID`,`Book_ID`),

  KEY `Book_ID` (`Book_ID`),

  CONSTRAINT `records_ibfk_1` FOREIGN KEY (`Book_ID`) REFERENCES `books` (`book_ID`),

  CONSTRAINT `records_ibfk_2` FOREIGN KEY (`Guest_ID`) REFERENCES `guests` (`Guest_ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

.....

回到 phpMyAdmin 網頁則以 SQL 命令刪除外來鍵如下:

Alter Table Books

   Drop Foreign key `books_ibfk_1`;

Alter Table Records

Drop Foreign key `records_ibfk_1`;

Alter Table Records

Drop Foreign key `records_ibfk_2`;

刪除完外來鍵之後,由 AppServ 功能選單內『設計』可觀察到各資料表之間都沒有聯繫。

12-3-4 驗證外來鍵Cascade 功能

(下載已建立完成 Temple-Library 資料庫)

吾人在『 Temple-Library』資料庫具有 Cascade 功能。吾人希望系統具有下類規範:

1.          Books 外來鍵(Pub_ID) 沒有宣告 Cascade 功能 (預設值為 Not Action),則表示當 Publishers 表內記錄有被 Books 表參考到時,則不允許刪除或更新,除非先除刪除 Books 記錄,再刪除 Publishers 記錄。(Update/Delete Not Action)

2.          Books 表中某筆資料被刪除,則 Records 表內相關紀錄也被刪除。(Delete Cascade)

3.          Guests 表中某筆記錄(Guest_ID) 變更時,Records 表內相關紀錄也隨之變更。(Update cascade)

4.          Guests 表內某筆記錄被刪除時,則 Records 表內相對的紀錄也被刪除。(Delete Cascade)

(A) 外來鍵設定

          依照上述規範,吾人規劃外來鍵具有 Cascade 的命令如下:

   /* 第一條規範 (預設值 Not Action)*/

Alter Table Books ADD Foreign key (Pub_ID) References publishers(Pub_ID);

/* 第二條規範 */

Alter Table Records ADD Foreign key (Book_ID) References Books(Book_ID)

On Delete Cascade;

/* 第三、四條規範 */

Alter Table Records ADD Foreign key (Guest_ID) References Guests(Guest_ID)

On Delete Cascade On Update Cascade;

重新建立之後,可觀察到各資料表之間的關係圖,如下:

   

(B) 驗證:Not Action (第一條規範)

Publishers 表中刪除某一出版社,觀察是否允許刪除,如不行,則先刪除 Books 有參考到記錄,再刪除 Publishers 中記錄。

觀察 Books 是否有『翻轉工作室』(Pub_ID = 3)的相關紀錄,再刪除 Publishers 表『真美出版公司』(Pub_ID = 3),記錄是否允許。

n   首先 Books 表內是否有『真美出版公司』的相關紀錄。如有,則刪除 publishers 表內『真美出版公司』記錄是否可行。

select *

from books

where Pub_id = (select Pub_ID

                            from publishers

                            where pub_name = "翻轉工作室");

Delete

from publishers

where pub_name = "翻轉工作室";

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

n   吾人必須先刪除掉 Books 有參考到 publishers 表『翻轉工作室』的所有記錄,才允許刪除 publishers 表內的『翻轉工作室』記錄

Delete

from Books

where pub_id = (select Pub_ID

                            from publishers

                            where pub_name = "翻轉工作室");

select *

from Books

where pub_id = (select Pub_ID

                            from publishers

                            where pub_name = "翻轉工作室");

傳回空值,表示 Books 表內相關 "翻轉工作室" 記錄被刪除

Delete

from publishers

where pub_name = "翻轉工作室";

 

select *

from publishers

where pub_name = "翻轉工作室";

傳回空值,表示 Publishers 表內 "翻轉工作室" 記錄被刪除

(C) 驗證:刪除某一書籍,Records 表是否隨之改變。(第二條規範)

Books 表中刪除『紅樓夢』書籍,觀察 Records 是否還有人租用該本書。

n   首先查詢『三國演義』的 Book_id 為何。

select Book_ID

from Books

where title = "三國演義";

n   再由 Books 表內刪除 Book_id = 1 的記錄,之後觀察 Records 記錄中是否還有接用該本書的記錄,如果沒有則表示 Records 跟隨著刪除相關紀錄,符合 Delete Cascade 之功能。

delete

from Books

where Book_id = "1";

select *

from Records

where Book_id= "1";

傳回空值,表示 Delete Cascade 正確

 (D) 驗證:變更某 Guest_IDRecords 表是否隨之改變。(第三條規範)

將『張彥宏』先生的 Guest_ID 改為 B002,觀察 Records 表內相關紀錄是否被更改。

n   首先查詢『張彥宏』的 Guest_id 為何,並觀察他的借書紀錄(Records)

select guest_ID

from Guests

where name= "張彥宏";

select *

from records

where guest_id = (select guest_ID

                from Guests

                where name= "張彥宏");

n   再由 Guests 表將『張彥宏』的 Guest_id  改為 B002,再觀察他的借書紀錄是否有所改變。之後觀察 Records 記錄中『張彥宏』借書記錄是否有改變,如果沒有則表示符合 Update Cascade 之功能。

update Guests

set guest_id = "B002"

where name= "張彥宏";

select *

from records

where guest_id = (select guest_ID

                              from Guests

                              where name= "張彥宏");

Book_ID 被變更,表示 Update Cascade 正確

 (E) 驗證:刪除某 Guest_IDRecords 表紀錄是否跟隨刪除。(第四條規範)

Guests 表中將『張彥宏』(Guest_ID=B002)先生記錄刪除,觀察 Records 表內是否還有它的借書紀錄。

n   首先查詢『張彥宏』的 Guest_id 為何,並觀察他的借書紀錄(Records)

select guest_ID

from Guests

where name= "張彥宏";

  

select *

from records

where guest_id = (select guest_ID

                             from Guests

                             where name= "張彥宏");

  

n   再由 Guests 表中刪除『張彥宏』記錄,再觀察 Records 記錄中『張彥宏』(Guest_id = "B002"是否還存在,如果沒有則表示符合 Delete Cascade 之功能。

delete

from Guests

where Guest_id = "B002";

 

select *

from records

where guest_id = " B002";

  傳回空值,表示 Delete Cascade 正確

翻轉工作室:粘添壽

 

資料庫系統概論(含邏輯設計)

 

 

翻轉電子書系列: