Database 外來鍵 cascade

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

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_ID，Records 表是否隨之改變。(第三條規範)

將『張彥宏』先生的 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_ID，Records 表紀錄是否跟隨刪除。(第四條規範)

由 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 正確

翻轉工作室：粘添壽

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

翻轉電子書系列：