Review: using foreign key constraints when updating databases - PHP

As I mentioned, the previous article of this series discussed how to utilize foreign key constraints to maintain the relationship between two sample InnoDB tables. The first one stored some simple blog entries, and the second one housed the comments made to those entries.

The neat part of this example is that defining a foreign key constraint on the child table allowed us to automatically delete all of the comments related to a specific blog post when this post was removed. You'll understand this better from the example, so here are the respective definitions of the mentioned tables, which establish a one-to-many relationship with each other:

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;

Apart from defining some simple fields on the two above InnoDB tables, the previous code sample uses a foreign key constraint to allow a cascading update of the rows of the “comments” table whenever the “id” key of the parent is updated too.

The line that defines the constraint on the “id” parent field is the following:

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

Aside from specifying a cascading update on the child table based on the operations performed on the parent, the InnoDB engine supports other actions. These include “NO ACTION” and “RESTRICT,” which will refuse any update or deletion on the parent table.

Now, returning to the previous example, and based on the definition of the sample MySQL tables, if they were 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')

Then, if for any reason it’s necessary to update the first blog entry, automatically all of its related comments will be updated also, simply by running the following SQL statement:

"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"

That was pretty neat, right? As I explained before, foreign key constraints permit you to delegate the maintenance of relationships between tables at the database level, which means writing less code for the application that interacts with the data layer.

In addition, it’s important to note that it’s possible to trigger deletions in a cascade, in a way similar to the case illustrated a moment ago. Thus, in the following section I’m going to reuse the two sample tables defined earlier to demonstrate how useful a foreign key constraint can be for deleting the comments related to a particular blog post when this entry is removed.

To see how this brand new example will be developed, click on the link shown below and keep reading.