MySQL foreign key constraint cascading Deletion

MySQL foreign key constraint cascading deletion when updating a database, the first table is used to store some simple blog data, and the second table is used to store comments related to these blogs. This example cleverly defines a foreign key constraint for the sub-table so that we can automatically delete all related comments when a blog post is deleted. The definitions of the two tables are given below. They establish a one-to-many relationship:

01DROP   TABLE   IF   EXISTS  `test`.`blogs`;0203CREATE   TABLE  `test`.`blogs` (0405`id`  INT ( 10 ) UNSIGNED AUTO_INCREMENT,0607`title`  TEXT ,0809`content`  TEXT ,1011`author`  VARCHAR ( 45 )  DEFAULT   NULL ,1213PRIMARY   KEY  (`id`)1415) ENGINE = InnoDB  DEFAULT  CHARSET = utf8;1617   1819DROP   TABLE   IF   EXISTS  `test`.`comments`;2021CREATE   TABLE  `test`.`comments` (2223`id`  INT ( 10 ) UNSIGNED AUTO_INCREMENT,2425`blog_id`  INT ( 10 ) UNSIGNED  DEFAULT   NULL ,2627`comment`  TEXT ,2829`author`  VARCHAR ( 45 )  DEFAULT   NULL ,3031PRIMARY   KEY  (`id`),3233KEY  `blog_ind` (`blog_id`),3435CONSTRAINT  `comments_ibfk_1`  FOREIGN   KEY  (`blog_id`)  REFERENCES  `blogs` (`id`)  ON   UPDATE   CASCADE3637) ENGINE = InnoDB  DEFAULT  CHARSET = utf8;

 

In addition to defining some simple fields for the above two InnoDB tables, the above Code also uses a foreign key constraint, so that whenever the "id" Key of the parent table is updated, the contents of the comments table are also updated in cascade mode. The code for defining constraints for the parent field "id" is as follows:

1CONSTRAINT  `comments_ibfk_1`  FOREIGN   KEY  (`blog_id`)  REFERENCES  `blogs` (`id`)  ON   UPDATE   CASCADE

 

In addition to cascade updates to child tables based on the Operations completed by the parent table, the InnoDB engine can also perform other operations, including "no action" and "RESTRICT ", in this way, even if the parent table is updated or deleted, no operations will be performed on the child table. Now, according to the definition of the MySQL table above, fill in the following data:

1INSERT   INTO  blogs (id, title, content, author)  VALUES  ( NULL , ' Title of the first blog entry ' ,  ' Content of the first blog entry ' ,  ' Tom ' )23INSERT   INTO  comments (id, blog_id, comment, author)  VALUES  ( NULL ,  1 ,  ' Commenting first blog entry ' ,  ' Susan Norton ' ), ( NULL ,  1 ,  ' Commenting first blog entry ' ,  ' Rose ' )

 

Then, for some reason, if we update the data of the first blog, all the comments related to the blog post will be automatically updated as long as the following SQL statement is run:

1UPDATE  blogs  SET  id  =   2 , title  =   ' Title of the first blog entry ' , content  =   ' Content of the first blog entry ' , author  =   ' John Doe '   WHERE  id  =   1

 

This looks pretty good, right? As mentioned above, foreign key constraints allow you to delegate the maintenance of the relationship between tables to the database layer, which means that you can save a lot of code when writing an application that interacts with the data layer. In addition, we can also trigger the cascade delete operation, which is similar to the preceding example. Therefore, we will continue to use the two sample tables defined earlier to demonstrate how to use foreign key constraints to delete comments when data in a blog article is deleted. Data deletion without foreign key constraints. To demonstrate the role of foreign key constraints in maintaining database integrity when the parent table data is deleted, We will recreate the previous example, use the MyISAM table this time. First, we need to define the data table. The specific code is as follows:

01DROP   TABLE   IF   EXISTS  `test`.`blogs`;0203CREATE   TABLE  `test`.`blogs` (0405`id`  INT ( 10 ) UNSIGNED AUTO_INCREMENT,0607`title`  TEXT ,0809`content`  TEXT ,1011`author`  VARCHAR ( 45 )  DEFAULT   NULL ,1213PRIMARY   KEY  (`id`)1415) ENGINE = MyISAM  DEFAULT  CHARSET = utf8;1617   1819DROP   TABLE   IF   EXISTS  `test`.`comments`;2021CREATE   TABLE  `test`.`comments` (2223`id`  INT ( 10 ) UNSIGNED AUTO_INCREMENT,2425`blog_id`  INT ( 10 ) UNSIGNED  DEFAULT   NULL ,2627`comment`  TEXT ,2829`author`  VARCHAR ( 45 )  DEFAULT   NULL ,3031PRIMARY   KEY  (`id`)3233) ENGINE = MyISAM  DEFAULT  CHARSET = utf8;

 

Now, we have created two sample tables. Note that they use the default MyISAM database engine, so foreign key constraints are not supported. The two tables are defined to form the data layer of the blog application. Next we will fill in some data in the table. The code used is as follows:

1INSERT   INTO  blogs (id, title, content, author)  VALUES  ( NULL , ' Title of the first blog entry ' ,  ' Content of the first blog entry ' ,  ' Tom ' )23INSERT   INTO  comments (id, blog_id, comment, author)  VALUES  ( NULL ,  1 ,  ' Commenting first blog entry ' ,  ' Susan Norton ' ), ( NULL ,  1 ,  ' Commenting first blog entry ' ,  ' Rose ' )

 

In fact, the code snippet above simulates the data related to a blog article inserted in the blogs table when a blog application is running and a blog post is posted and commented, insert comments into the subtable. Now, if we delete this blog, the related comments should also be deleted. But how should we do it? Take the SQL statement as an example to explain how to complete this task:

1DELETE   FROM  blogs  WHERE  id  =   12DELETE   FROM  comments  WHERE  blog_id  =   1<u></u>

 

Of course, in actual circumstances, we should execute the two Delete statements through the server language, instead of using the original SQL command. But here is just an example, you don't have to consider that much. I think you have understood how to delete blog data and related comments when using the MyISAM table. Therefore, we will recreate this example, but this time we will allow the data table to use the InnoDB Storage engine and a simple foreign key constraint. Data deletion when foreign key constraints are used is just like you can use foreign key constraints to cascade update data, InnoDB tables also support cascade deletion, this is extremely useful for maintaining the consistency of data tables with specific relationships. The following example shows how to redefine two tables:

01DROP TABLE  IF  EXISTS `test`.`blogs`;0203CREATE TABLE `test`.`blogs` (0405`id`  INT ( 10 ) UNSIGNED AUTO_INCREMENT,0607`title` TEXT,0809`content` TEXT,1011`author` VARCHAR( 45 ) DEFAULT  NULL ,1213PRIMARY KEY (`id`)1415) ENGINE = InnoDB DEFAULT CHARSET = utf8;1617   1819DROP TABLE  IF  EXISTS `test`.`comments`;2021CREATE TABLE `test`.`comments` (2223`id`  INT ( 10 ) UNSIGNED AUTO_INCREMENT,2425`blog_id`  INT ( 10 ) UNSIGNED DEFAULT  NULL ,2627`comment` TEXT,2829`author` VARCHAR( 45 ) DEFAULT  NULL ,3031PRIMARY KEY (`id`),3233KEY `blog_ind` (`blog_id`),3435CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`blog_id`) REFERENCES `blogs` (`id`)  ON  DELETE CASCADE3637) ENGINE = InnoDB DEFAULT CHARSET = utf8;

 

Now, the two tables blogs and comments that constitute the data layer of our fictitious blog application will use the InnoDB Storage engine. This means that they can use the foreign key constraint to delete all comments related to a blog when the blog is deleted. The SQL statement that causes cascading deletion is as follows:

1CONSTRAINT  `comments_ibfk_1`  FOREIGN   KEY  (`blog_id`)  REFERENCES  `blogs` (`id`)  ON   DELETE   CASCADE

 

Now, because this constraint has been applied to the "id" Field of the blog table, it is very easy to DELETE comments while deleting the blog, just like running a DELETE command, the details are as follows:

1DELETE   FROM  blogs  WHERE  id  =   1

 

As we can see, now things are much easier. From this example, you can imagine that when the data layer uses foreign key constraints to maintain the integrity and consistency of the relations between tables at the database level, how simple it is to develop an application that interacts with such a data layer.