Redefining the previous InnoDB tables - PHP

Enabling support for cascading updates and deletions within the “comments” MySQL table that you saw before is only a matter of modifying the segment of its definition that specifies the corresponding foreign key constraint.

Nonetheless, you'll understand this process better from seeing some functional SQL code. Thus, examine the enhanced definition of the “comments” table, which now is as follows:

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 ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

As I explained a few moments ago, the only change introduced into the definition of the “comments” table is the SQL clause that triggers both updates and deletions in cascade when the same operations are performed on the associated key in the “blogs” table. That was really simple to achieve, wasn’t it?

Now that the foreign key constraint specified within the “comments” table has been properly modified, it’s time to build a brand new script, which will use the already familiar MySQL abstraction class to perform the previously-mentioned cascading updates and removal of database rows.

To learn how this script will be developed, go ahead and read the final section of this tutorial. We’re almost finished here!