玩轉(zhuǎn)MySQL的外鍵約束之級聯(lián)刪除篇

admin126com

如今，許多關(guān)系型數(shù)據(jù)庫管理系統(tǒng)都提供了外鍵約束這一強大的功能特性，它能夠幫助我們自動地觸發(fā)指定的動作，諸如刪掉、更新數(shù)據(jù)庫表的記錄等，從而維護各數(shù)據(jù)庫表之間預(yù)定義的關(guān)系。本文將演示如何在MySQL中利用外鍵約束以級聯(lián)方式刪除數(shù)據(jù)。
　　對于PHP開發(fā)人員來說，在MySQL中使用InnoDB表時可以利用外鍵約束提供的許多的便利之處，盡管MySQL宣布將來的版本支持MyISAM表。本文將演示當更新和刪除父表數(shù)據(jù)時如何維護一個數(shù)據(jù)庫的完整性。
　　一、簡介
　　在上一篇文章中，我們講解了如何在MySQL的InnoDB表中結(jié)合使用外鍵約束，即當父表中的數(shù)據(jù)更新的同時如何觸發(fā)對子表數(shù)據(jù)的級聯(lián)更新操作。說老實話，從字面上描述級聯(lián)更新過程有些令人費解，如果通過SQL代碼來演示的話則要輕松得多。那么，我們先用文章做簡單表述，然后給出具體的代碼。在最簡單的情況下，這個過程首先要創(chuàng)建一個父表和一個子表，在子表中定義一個外鍵，然后規(guī)定當父表的數(shù)據(jù)更新時將發(fā)生什么動作。
　　使用外鍵約束的主要好處是，我們可以在數(shù)據(jù)庫級別很輕松地對表之間的關(guān)系進行處理，而無需在與數(shù)據(jù)層交互的應(yīng)用程序內(nèi)部實現(xiàn)這些邏輯。值得一提的是，這對于性能來說，可能會有一些損失，尤其是程序規(guī)模較大的時候。當然，數(shù)據(jù)庫性能問題不在本文的討論范圍之內(nèi)，下面我們演示如何使用外鍵約束在父表數(shù)據(jù)被刪除時觸發(fā)對子表數(shù)據(jù)的級聯(lián)刪除操作。下面看看我們是如何將這些晦澀難懂的術(shù)語轉(zhuǎn)換為一目了然的SQL代碼的。
　　二、在更新數(shù)據(jù)庫時使用外鍵約束
　　正如前面介紹的一樣，上一篇文章論述如何運用外鍵約束維護兩個示例InnoDB表之間的關(guān)系：第一個表
存儲
一些簡單博客數(shù)據(jù)，而第二個表則存放這些博客的有關(guān)評論。這例子的巧妙之處在于，它給子表定義了一個外鍵約束，從而允許我們在博客文章被刪除時自動地刪除有關(guān)的所有評論。下面給出這兩個表的定義，它們建立了一個一對多的關(guān)系：
DROP TABLE IF EXISTS `test`.`blogs`;
CREATE TABLE `test`.`blogs` (
`id` INT(10) UNSIGNED AUTO_INCREMENT,
`title` TEXT,
`content` TEXT,
`author` VARCHAR(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
DROP TABLE IF EXISTS `test`.`comments`;
CREATE TABLE `test`.`comments` (
`id` INT(10) UNSIGNED AUTO_INCREMENT,
`blog_id` INT(10) UNSIGNED DEFAULT NULL,
`comment` TEXT,
`author` VARCHAR(45) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `blog_ind` (`blog_id`),
CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`blog_id`) REFERENCES `blogs` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
　　除了給以上兩個InnoDB表定義一些簡單字段外，上述的代碼還使用了一個外鍵約束，使得每當父表的“id”鍵更新時，表comments的相應(yīng)內(nèi)容也會級聯(lián)更新。給父字段“id”定義約束的代碼如下所示：
CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`blog_id`) REFERENCES `blogs` (`id`) ON UPDATE CASCADE
　　InnoDB引擎除了可以規(guī)定根據(jù)父表完成的操作對子表進行的級聯(lián)更新以外，還可以執(zhí)行其他的操作，包括“NO ACTION”和“RESTRICT”，這樣即使父表發(fā)生更新或者刪除操作，也不會引起對子表的任何操作。
　　現(xiàn)在，根據(jù)上面的MySQL表的定義，填充如下所示的數(shù)據(jù)：
INSERT INTO blogs (id, title, content, author) VALUES (NULL,'Title of the first blog entry', 'Content of the first blog entry', 'Tom')
INSERT INTO comments (id, blog_id, comment, author) VALUES (NULL, 1, 'Commenting first blog entry', 'Susan Norton'), (NULL, 1, 'Commenting first blog entry', 'Rose')
　　然后，由于某種原因，我們更新了第一個博客數(shù)據(jù)，那么只要運行下列SQL語句，與該博客文章有關(guān)的所有評論也會隨之自動更新：
"UPDATE blogs SET id = 2, title = 'Title of the first blog entry', content = 'Content of the first blog entry', author = 'John Doe' WHERE id = 1"
　　這看起來非常不錯，對吧?前面講過，外鍵約束容許您將表之間的關(guān)系的維護工作委托給數(shù)據(jù)庫層，這意味著編寫與數(shù)據(jù)層交互的應(yīng)用程序時可以省去不少的代碼。
　　此外，我們也可以觸發(fā)級聯(lián)刪除操作，這與前面演示的情形非常類似。因此，下面我們繼續(xù)使用早先定義的兩個示例表來演示當某篇博客文章的數(shù)據(jù)被刪除時，如何利用外鍵約束刪除相應(yīng)的評論。
　　三、不使用外鍵約束時的數(shù)據(jù)刪除
　　為了說明當父表數(shù)據(jù)被刪除時，外鍵約束在維護數(shù)據(jù)庫完整性方面發(fā)揮的作用，我們將重建前面的例子，這次使用MyISAM表。首先，我們需要定義數(shù)據(jù)表，具體代碼如下所示：
DROP TABLE IF EXISTS `test`.`blogs`;
CREATE TABLE `test`.`blogs` (
`id` INT(10) UNSIGNED AUTO_INCREMENT,
`title` TEXT,
`content` TEXT,
`author` VARCHAR(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  
DROP TABLE IF EXISTS `test`.`comments`;
CREATE TABLE `test`.`comments` (
`id` INT(10) UNSIGNED AUTO_INCREMENT,
`blog_id` INT(10) UNSIGNED DEFAULT NULL,
`comment` TEXT,
`author` VARCHAR(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
　　好了，我們已經(jīng)建好了兩個示例表，需要注意的是，它們使用的是默認的MyISAM數(shù)據(jù)庫引擎，所以不支持外鍵約束。
　　定義的這兩個表構(gòu)成了博客應(yīng)用程序的數(shù)據(jù)層，接下來我們在其中填上一些數(shù)據(jù)，所用的代碼如下所示：
INSERT INTO blogs (id, title, content, author) VALUES (NULL,'Title of the first blog entry', 'Content of the first blog entry', 'Tom')
INSERT INTO comments (id, blog_id, comment, author) VALUES (NULL, 1, 'Commenting first blog entry', 'Susan Norton'), (NULL, 1, 'Commenting first blog entry', 'Rose')
　　實際上，以上代碼片斷模擬了博客應(yīng)用程序運行時，我們發(fā)布了博客并有人張貼評論時，程序在表blogs中插入一篇博客文章的有關(guān)數(shù)據(jù)，并在子表中插入有關(guān)評論的過程�，F(xiàn)在，如果我們刪除了這篇博客，那么有關(guān)的評論也應(yīng)該隨之刪除。
　　但是，我們該如何去做呢?別急，下面我們以SQL語句為例說明如何完成此任務(wù)：
DELETE FROM blogs WHERE id = 1
DELETE FROM comments WHERE blog_id = 1
　　當然，在實際情況下，我們應(yīng)該通過
服務(wù)器
端語言來執(zhí)行這兩個刪除語句，而不是使用原始的SQL命令;但是這里只是舉例之用，就不用考慮這么多了。
　　我想您現(xiàn)在已經(jīng)弄明白了使用MyISAM表時如何刪除博客數(shù)據(jù)，以及有關(guān)的評論。因此,接下來我們將重新構(gòu)建這個例子，不過這次我們將讓數(shù)據(jù)表使用InnoDB存儲引擎和一個簡單的外鍵約束。
　　四、使用外鍵約束時的數(shù)據(jù)刪除
　　恰如您可以使用外鍵約束級聯(lián)更新數(shù)據(jù)一樣，InnoDB表還支持級聯(lián)刪除，這對于維護那些具有特定關(guān)系的數(shù)據(jù)表的一致性極為有用。
　　下面我們舉例說明，現(xiàn)在重新定義兩個表，如下所示：
DROP TABLE IF EXISTS `test`.`blogs`;
CREATE TABLE `test`.`blogs` (
`id` INT(10) UNSIGNED AUTO_INCREMENT,
`title` TEXT,
`content` TEXT,
`author` VARCHAR(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
DROP TABLE IF EXISTS `test`.`comments`;
CREATE TABLE `test`.`comments` (
`id` INT(10) UNSIGNED AUTO_INCREMENT,
`blog_id` INT(10) UNSIGNED DEFAULT NULL,
`comment` TEXT,
`author` VARCHAR(45) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `blog_ind` (`blog_id`),
CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`blog_id`) REFERENCES `blogs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
　　現(xiàn)在，組成我們虛構(gòu)的博客應(yīng)用程序的數(shù)據(jù)層的兩個表blogs和comments將使用InnoDB存儲引擎。這意味著，它們能利用外鍵約束來刪除與某博客有關(guān)的所有評論，當該博客被刪除的時候。
　　引起級聯(lián)刪除的SQL語句如下所示：
CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`blog_id`) REFERENCES `blogs` (`id`) ON DELETE CASCADE
　　現(xiàn)在，由于這個約束已經(jīng)施加于blog表的“id”字段，所以在刪除博客的同時清除有關(guān)評論將非常簡單，就像運行一個DELETE命令一樣，具體如下所示：
DELETE FROM blogs WHERE id = 1
　　我們看到，現(xiàn)在事情變得簡單多了。從這個例子您就可以想象得出，當數(shù)據(jù)層使用利用外鍵約束在數(shù)據(jù)庫級別維護各表之間關(guān)系的完整性和一致性的數(shù)據(jù)表的時候，開發(fā)與這樣的數(shù)據(jù)層交互的應(yīng)用程序是多么的簡單。
　　五、小結(jié)
　　在本文中，我們首先回顧了如何在數(shù)據(jù)表更新時使用外鍵約束，然后為讀者詳細介紹了當父表執(zhí)行了刪除操作時，如何使用外鍵約束觸發(fā)對子表的級聯(lián)刪除操作。您也許還記得，在上一篇文章中我們介紹了如何在父表更新時讓子表觸發(fā)相同的操作，那么能不能用外鍵約束同時處理刪除和更新操作呢?您可能已經(jīng)猜到了，答案是肯定的，具體的介紹請見下一篇文章。



本文來自ChinaUnix博客，如果查看原文請點：http://blog.chinaunix.net/u2/86974/showart_2146067.html