Enabling support for cascading row deletions - PHP

In the earlier section, you recalled how to trigger cascading updates of records of a child table when rows in the associated parent were updated. The next step involves producing a similar effect, but this time with record deletions.

Again, it’s worthwhile to mention that a similar example was coded in a previous tutorial using raw SQL, but in this case I’m going to use the PHP 5-based abstraction class that was shown a few moments ago.

That being clarified, it’s time to redefine the pertinent blog tables, which will be created in the following way:

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 shown before, the definitions of the InnoDB tables that compose the storage mechanism of the blogger program remain nearly the same, with the exception of the following line:

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

Obviously, this part of the table’s definition specifies the deletion in cascade of the comments related to a particular blog entry, when the entry is removed. This permits you to maintain the integrity of the relationship between the tables.

Now that these tables have been properly modified, what’s the next step? Well, since cascading deletions of database rows are usually triggered by a server-side language, in the last segment of this tutorial I’m going to explain how to perform this task with the MySQL abstraction class was shown previously.

So, go ahead and read the following segment. It’s only one click away.