如今,许多关系型数据库管理系统都提供了外键约束这一强大的功能特性,它能够帮助我们自动地触发指定的动作,诸如删掉、更新数据库表的记录等,从而维护各数据库表之间预定义的关系。本文将演示如何在MySQL中利用外键约束以级联方式删除数据。
对于PHP开发人员来说,在MySQL中使用InnoDB表时可以利用外键约束提供的许多的便利之处,尽管MySQL宣布将来的版本支持MyISAM表。本文将演示当更新和删除父表数据时如何维护一个数据库的完整性。
在上一篇文章中,我们讲解了如何在MySQL的InnoDB表中结合使用外键约束,即当父表中的数据更新的同时如何触发对子表数据的级联更新操作。说老实话,从字面上描述级联更新过程有些令人费解,如果通过SQL代码来演示的话则要轻松得多。那么,我们先用文章做简单表述,然后给出具体的代码。在最简单的情况下,这个过程首先要创建一个父表和一个子表,在子表中定义一个外键,然后规定当父表的数据更新时将发生什么动作。
使用外键约束的主要好处是,我们可以在数据库级别很轻松地对表之间的关系进行处理,而无需在与数据层交互的应用程序内部实现这些逻辑。值得一提的是,这对于性能来说,可能会有一些损失,尤其是程序规模较大的时候。当然,数据库性能问题不在本文的讨论范围之内,下面我们演示如何使用外键约束在父表数据被删除时触发对子表数据的级联删除操作。下面看看我们是如何将这些晦涩难懂的术语转换为一目了然的SQL代码的。
正如前面介绍的一样,上一篇文章论述如何运用外键约束维护两个示例InnoDB表之间的关系:第一个表存储一些简单博客数据,而第二个表则存放这些博客的有关评论。这例子的巧妙之处在于,它给子表定义了一个外键约束,从而允许我们在博客文章被删除时自动地删除有关的所有评论。下面给出这两个表的定义,它们建立了一个一对多的关系:
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 =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` FOREIGNKEY(`blog_id`)REFERENCES`blogs` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT =utf8;
除了给以上两个InnoDB表定义一些简单字段外,上述的代码还使用了一个外键约束,使得每当父表的“id”键更新时,表comments的相应内容也会级联更新。给父字段“id”定义约束的代码如下所示:
CONSTRAINT `comments_ibfk_1`FOREIGNKEY(`blog_id`)REFERENCES `blogs` (`id`) ONUPDATECASCADE
InnoDB引擎除了可以规定根据父表完成的操作对子表进行的级联更新以外,还可以执行其他的操作,包括“NOACTION”和“RESTRICT”,这样即使父表发生更新或者删除操作,也不会引起对子表的任何操作。
现在,根据上面的MySQL表的定义,填充如下所示的数据:
INSERT INTO blogs (id, title,content,author)VALUES(NULL,Title of the first blog entry,Content of thefirstblogentry, Tom)
INSERT INTO comments (id, blog_id, comment, author)VALUES(NULL,1,Commenting first blog entry, Susan Norton),(NULL,1,Commentingfirst blog entry, Rose)
然后,由于某种原因,我们更新了第一个博客数据,那么只要运行下列SQL语句,与该博客文章有关的所有评论也会随之自动更新:
UPDATE blogs SET id = 2, title = Title ofthefirstblogentry, content = Content of the first blogentry,author =JohnDoe WHERE id = 1
这看起来非常不错,对吧?前面讲过,外键约束容许您将表之间的关系的维护工作委托给数据库层,这意味着编写与数据层交互的应用程序时可以省去不少的代码。
此外,我们也可以触发级联删除操作,这与前面演示的情形非常类似。因此,下面我们继续使用早先定义的两个示例表来演示当某篇博客文章的数据被删除时,如何利用外键约束删除相应的评论。
为了说明当父表数据被删除时,外键约束在维护数据库完整性方面发挥的作用,我们将重建前面的例子,这次使用MyISAM表。首先,我们需要定义数据表,具体代码如下所示:
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 =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 =utf8;
好了,我们已经建好了两个示例表,需要注意的是,它们使用的是默认的MyISAM数据库引擎,所以不支持外键约束。
定义的这两个表构成了博客应用程序的数据层,接下来我们在其中填上一些数据,所用的代码如下所示:
INSERT INTO blogs (id, title,content,author)VALUES(NULL,Title of the first blog entry,Content of thefirstblogentry, Tom)
INSERT INTO comments (id, blog_id, comment, author)VALUES(NULL,1,Commenting first blog entry, Susan Norton),(NULL,1,Commentingfirst blog entry, Rose)
实际上,以上代码片断模拟了博客应用程序运行时,我们发布了博客并有人张贴评论时,程序在表blogs中插入一篇博客文章的有关数据,并在子表中插入有关评论的过程。现在,如果我们删除了这篇博客,那么有关的评论也应该随之删除。
但是,我们该如何去做呢?别急,下面我们以SQL语句为例说明如何完成此任务:
DELETE FROM blogs WHERE id = 1
DELETE FROM comments WHERE blog_id = 1
当然,在实际情况下,我们应该通过服务器端语言来执行这两个删除语句,而不是使用原始的SQL命令;但是这里只是举例之用,就不用考虑这么多了。
我想您现在已经弄明白了使用MyISAM表时如何删除博客数据,以及有关的评论。因此,接下来我们将重新构建这个例子,不过这次我们将让数据表使用InnoDB存储引擎和一个简单的外键约束。
恰如您可以使用外键约束级联更新数据一样,InnoDB表还支持级联删除,这对于维护那些具有特定关系的数据表的一致性极为有用。
下面我们举例说明,现在重新定义两个表,如下所示:
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 =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` FOREIGNKEY(`blog_id`)REFERENCES`blogs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT =utf8;
现在,组成我们虚构的博客应用程序的数据层的两个表blogs和comments将使用InnoDB存储引擎。这意味着,它们能利用外键约束来删除与某博客有关的所有评论,当该博客被删除的时候。
引起级联删除的SQL语句如下所示:
CONSTRAINT `comments_ibfk_1`FOREIGNKEY(`blog_id`)REFERENCES `blogs` (`id`) ONDELETECASCADE
现在,由于这个约束已经施加于blog表的“id”字段,所以在删除博客的同时清除有关评论将非常简单,就像运行一个DELETE命令一样,具体如下所示:
DELETE FROM blogs WHERE id = 1
我们看到,现在事情变得简单多了。从这个例子您就可以想象得出,当数据层使用利用外键约束在数据库级别维护各表之间关系的完整性和一致性的数据表的时候,开发与这样的数据层交互的应用程序是多么的简单。
在本文中,我们首先回顾了如何在数据表更新时使用外键约束,然后为读者详细介绍了当父表执行了删除操作时,如何使用外键约束触发对子表的级联删除操作。您也许还记得,在上一篇文章中我们介绍了如何在父表更新时让子表触发相同的操作,那么能不能用外键约束同时处理删除和更新操作呢?您可能已经猜到了,答案是肯定的,具体的介绍请见下一篇文章。
分享到:
相关推荐
在数据库MySQL中利用外键实现级联删除、更新等方面的内容。有兴趣可以看看。。
主要介绍了MySQL使用外键实现级联删除与更新的方法,详细分析了mysql数据库与表的创建、数据插入、查询以及外链的使用与级联操作相关技巧,需要的朋友可以参考下
mysql级联更新和级联删除,区分有级联和没级联操作的区别之处
用于查询某个数据库下所有表的外键约束情况,以便统一分析外键约束是否合理...主要查询出外键的,是否级联删除,级联更新,强制外键约束,强制复制约束始终状态。如想使用别的状态,请自行添加。下载的童鞋别下载错了。
外键是用来实现参照完整性的,不同的外键约束方式将可以使两张表紧密的结合起来,特别是修改或者删除的级联操作将使得日常的维护工作更加轻松。 这里以MySQL为例,总结一下3种外键约束方式的区别和联系。 ...
sql存在检测、建库、建表、约束、外键、级联删除[文].pdf
sql_存在检测、建库、_建表、约束、外键、级联删除[归类].pdf
MySQL通过外键约束来保证表与表之间的数据的完整性和准确性。 外键的使用条件: 1.两个表必须是InnoDB表,MyISAM表暂时不支持外键(据说以后的版本有可能支持,但至少目前不支持); 2.外键列必须建立了索引,...
JSP+AJAX+MYSQL实现二级级联菜单
利用sql server自带的级联删除、级联更新功能,即其外键约束途径 alter table dbo.C add constraint FK_C_A_AID foreign key(AID) references dbo.A(AID) on delete cascade on update cascade go alter table dbo.C...
数据是从mySql数据库中动态读取,运行sql文件即可,经测试是没问题的……
mysql中的cascade级联,set null,restrict限制 都是什么意思,有何区别.zip
MSserver自关联表的级联删除,使用触发器对自关联树形结构的表进行级联删除,文有几点教训,有编写脚本的初衷。
LINQ通过外键增加删除数据库主从表的示例, 适合刚刚接触LINQ的人使用. 示例使用VS2008编写, 内置了数据库文件, 直接打开website就可以, 不支持VS2003和VS2005
最新最完整的中英法文ISO3166世界行政区划MySQL级联表,包含中国
hibernate many-to-many级联保存,级联更新,级联删除
(1)只有InnoDB类型的表才可以使用外键,mysql默认是MyISAM,这种类型不支持外键约束 (2)外键的好处:可以使得两张表关联,保证数据的一致性和实现一些级联操作; (3)外键的作用: 保持数据一致性,完整性,...
世界国家城市级联数据库mySql,包含中日英三种语言切换,中国精确到省市县,国外精确到城市,适合国际化项目 包含两个资源: 1:名称准确,三种语言独立级联。 2:日文英文在中文标准版基础上翻译,同一地区三种语言的...