Delegating update operations to the database - PHP

As I explained at the end of the previous section, it’s possible to rebuild the earlier example by using foreign key constraints and InnoDB tables, instead of the default MyISAM type that you saw before. Based on this idea, the first change that must be made is redefining the two sample tables, so they can use that specific database engine.

The following SQL code does that:

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

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

From the previous code sample, it’s clear to see that the first difference is that the two tables now use the InnoDB storage engine, which supports foreign key constraints.

Other than that, you should pay close attention to the following line within the definition of the “comments” table:

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

Actually, this statement instructs MySQL to update the values of the “blog_id” foreign key on the “comments” table, when the ID of the “blogs” table is updated. In other words, what I’m doing here is delegating the maintenance of the database integrity to MySQL, meaning that when a specific blog entry is updated, its associated comments also will reflect this change immediately, instead of implementing this functionality at the application level. Pretty neat, huh?

Now that the two sample MySQL tables have been defined that way, updating both of them would be as simple as running one single “UPDATE” SQL statement, like this:

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

As I mentioned before, there’s no need to update the “comments” table, since MySQL will take care of doing that for you automatically. In addition, it’s possible to tell MySQL not to do anything when attempting to update rows on the “blogs” table either by removing the “ON UPDATE” part of the query, or by specifying “NO ACTION” and “RESTRICT.” Other options are also available, but for now are out of the scope of this tutorial.

Now that you hopefully have a clear idea of how to work with foreign key constraints when using InnoDB tables in MySQL, I encourage you to develop your own examples to acquire a more solid background on this handy database feature.

Final thoughts

That’s all for the moment. In this first part of the series, I provided you with a basic introduction to using foreign key constraints with InnoDB tables in MySQL. As you saw by the example created earlier, triggering a “cascading” update on the records of a child table when updating rows in a parent table is a breeze, which also helps to get rid of implementing this capability inside the application that handles the data layer.

Of course, it’s also feasible to raise the same cascading effect when deleting rows on a parent table, but the full details of this process will be covered in the next tutorial. So, my suggestion is simple: don’t miss that tutorial!