Review: deleting database rows in cascade - PHP

Before I explain how to perform updates and deletions in cascade in the rows of a child table, I’d like to reintroduce the example developed in the preceding tutorial, It showed how to use foreign key constraints to delete only the rows of a table that stored comments on some blog posts in response to the deletion of a particular blog entry.

Having said that, here’s how the tables that comprised this basic example were initially defined:

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;

As depicted by the above code sample, I defined two simple InnoDB tables, where the first one is used for storing blogs post, and the second one holds all of the comments made on each of those posts. This typical example of a one-to-many relationship between a pair of tables is very convenient for demonstrating the advantages in using a single foreign key constraint in a concrete situation.

Now, say that the tables are populated with the following data:

INSERT INTO blogs (id, title, content, author) VALUES (NULL,'Title of the first blog entry', 'Content of the first blog entry', 'Alejandro Gervasio')

INSERT INTO comments (id, blog_id, comment, author) VALUES (NULL, 1, 'Commenting first blog entry', 'Susan Norton'), (NULL, 1, 'Commenting first blog entry', 'Mary Wilson')

Since the tables are no longer empty structures, that’s definitely a significant advance. But what if I need to remove the first (and only) entry of the “blogs” table without delegating this process to the application level? It’s really as simple as running the following statement:

DELETE FROM blogs WHERE id = 1

Thanks to the definition of a simple foreign key constraint, the previous “DELETE” command not only will delete the first blog post, but all of the comments related to it, in a single step. Pretty neat, right?

Nevertheless, as I mentioned in the introduction of this article, the InnoDB storage engine also permits you to perform both cascading updates and deletes within the definition of a particular table. Therefore, assuming that you’re interested in learning how to accomplish this in a few easy steps, in the section to come I’m going to create another example, similar to the one you saw earlier that will take advantage of this handy feature.

Now, click on the link that appears below and keep reading.