Triggering Cascading Updates and Deletions with Foreign Key Constraints

Welcome to the final installment of a series on working with foreign key constraints in MySQL. With a respectable number of code samples, this series walks you through the basics of using foreign key constraints in MySQL tables and teaches you how to use them specifically for maintaining the integrity of relational databases.

And now that you’ve been introduced to the goal of this group of articles, it’s time to refresh the topics that were covered in the last tutorial. In the penultimate part of the series, I showed you how to trigger the deletion in cascade of rows of a child MySQL table after performing the same operation on the records of its corresponding parent table.

In this example, the tables that I utilized to demonstrate how useful foreign key constraints can be comprised the database schema of a fictional blog program.  It allowed some hypothetical users to post comments on a specific blog entry. Of course, the most relevant detail to stress here is that the cascading deletion of comments was triggered and handled internally by MySQL, in this way allowing you to implement a much simpler logic within the blog application.

Nevertheless, foreign key constraints allow you to handle both updates and deletions in the same MySQL table with the same ease, so in this final chapter of the series I’m going to explain how to achieve this by using the sample MySQL abstraction class coded in the preceding tutorial.

So, are ready to tackle the last chapter of this series on working with foreign key constraints in MySQL? Then start reading now!

{mospagebreak title=Review: cascading updates with MySQL InnoDB tables}

It’s quite possible that you haven’t yet had the chance to read the previous article of the series. It demonstrated how to use foreign key constraints within a couple of InnoDB tables to delete in cascade some rows within one of them. So, taking into account this possibility, below I included the entire source code for this example, starting with the definitions of the tables, which look like this:

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;

From the definition of these tables, it’s easy to see that both of them compose the database layer of a basic blog program, where the first one stores blog entries and the last one houses the comments that could have been made on each blog post.

Naturally, the most relevant thing to notice here is the SQL statement that specifies the cascading deletions of rows in the “comments” table, when the related blog posts in the parent table are removed.

So far, so good. Having shown the respective definitions of the above two InnoDB tables, here’s the complete signature of the MySQL abstraction class built in PHP 5, which is responsible for interacting with them:

<?php

 

class MySQL

{

private $result = NULL;

private $link = NULL;

 

// connect to MySQL

public function __construct($host, $user, $password, $database)

{

if (FALSE === ($this->link = mysqli_connect($host, $user, $password, $database)))

{

throw new Exception(‘Error : ‘ . mysqli_connect_error());

}

}

 

// perform query

public function query($query)

{

if (is_string($query) AND empty($query) === FALSE)

{

if (FALSE === ($this->result = mysqli_query($this->link, $query)))

{

throw new Exception(‘Error performing query ‘ . $query . ‘ Error message :’ .mysqli_error($this->link));

}

}

}

 

// fetch row from result set

public function fetch()

{

if (FALSE === ($row = mysqli_fetch_object($this->result)))

{

mysqli_free_result($this->result);

return FALSE;

}

return $row;

}

 

// get insertion ID

public function getInsertID()

{

return mysqli_insert_id($this->link);

}

// count rows in result set

public function countRows()

{

if ($this->result !== NULL)

{

return mysqli_num_rows($this->result);

}

}

// implement destructor to close the database connection

function __destruct()

{

mysqli_close($this->link);

}

}

?>

Certainly, I’m not going to waste your time (and mine) explaining how the previous “MySQL” class functions, since its source code is extremely easy to follow. Instead, I will build a short script that uses the class to insert, first, a blog entry into the first “blogs” table, and second, a couple of comments into the second table.

Finally, and only for example purposes, the blog entry will be deleted, which also will automatically remove the comments associated with it. Having explained that, here’s the script that performs all those tasks:

require_once ‘mysqlclass.php';

$db = new MySQL(‘host’, ‘user’, ‘password’, ‘test’);

// insert new entry into ‘blogs’ database table

$db->query("INSERT INTO blogs (id, title, content, author) VALUES (NULL,’Title of the first blog entry’, ‘Content of the first blog entry’, ‘Alejandro Gervasio’)");

 

$insid = $db->getInsertID();

// insert new comments into ‘comments’ database table

$db->query("INSERT INTO comments (id, blog_id, comment, author) VALUES (NULL, $insid, ‘Commenting first blog entry’, ‘Susan Norton’), (NULL, $insid, ‘Commenting first blog entry’, ‘Mary Wilson’)");

 

// delete row in ‘blogs'; rows in ‘comments’ table are deleted automatically

 

$db->query("DELETE FROM blogs WHERE id = ‘$insid’");

See how easy it is to maintain the integrity of the relationship existing between the two sample MySQL tables? If you still don’t see the nitty-gritty of this process, then the previous code fragment should dissipate all of your doubts quickly. Removing a specified blog post along with its related comments is as simple as running one single DELETE command in the first table and nothing else. MySQL automatically takes care of removing in cascade the corresponding comments.

At this stage, you surely understood how the earlier example works, so it’s time to learn how to use the MySQL abstraction class for triggering both cascading updates and deletions in the “comments” table.

This process was shown in a previous tutorial of the series using plain SQL, but in this particular case it’d be pretty useful to recreate it directly with PHP 5. Therefore, in the section to come I’m going to modify the definition of the “comments” MySQL table to enable within it the support for cascading updates and deletions.

Now, click on the link below and read the following segment.

{mospagebreak title=Redefining the previous InnoDB tables} 

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!

{mospagebreak title=Updating and deleting records in cascade within the same MySQL table}

If you’re like me, then you want to learn how to create a script in PHP 5 that shows how to work with the pair of InnoDB tables defined in the previous segment. So, below I wrote a small code snippet, which first populates those tables with a blog post and a couple of comments, and then updates that post, therefore updating the related comments, and finally deletes it, in this way removing the comments as well.

Does this sound a bit confusing to you? Well, don’t feel worried; just look at the script that does all those things in one single place. Here it is:

require_once ‘mysqlclass.php';

$db = new MySQL(‘host’, ‘user’, ‘password’, ‘test’);

// insert new entry into ‘blogs’ database table

$db->query("INSERT INTO blogs (id, title, content, author) VALUES (NULL,’Title of the first blog entry’, ‘Content of the first blog entry’, ‘Alejandro Gervasio’)");

 

$insid = $db->getInsertID();

// insert new comments into ‘comments’ database table

$db->query("INSERT INTO comments (id, blog_id, comment, author) VALUES (NULL, $insid, ‘Commenting first blog entry’, ‘Susan Norton’), (NULL, $insid, ‘Commenting first blog entry’, ‘Mary Wilson’)");

 

// update row in ‘blogs’ table; rows in ‘comments’ table are updated automatically

$db->query("UPDATE blogs SET id = 2, title = ‘Title of the first blog entry’, content = ‘Content of the first blog entry’, author = ‘John Doe’ WHERE id = ‘$insid’");

 

// delete row in ‘blogs’ table; rows in ‘comments’ table are deleted automatically

 

$db->query("DELETE FROM blogs WHERE id = ‘$insid’");

If you analyze in detail the above code sample, you’ll realize how useful foreign key constraints can be for maintaining at the database level the integrity of InnoDB tables linked through a specified relationship. In this case, the application is only responsible for updating and deleting blog entries, while those operations are executed automatically on the “comments” table by MySQL.

Of course, the tiny penalty in using InnoDB tables is that they’re slightly slower than those that rely on the MyISAM storage engine, but if you use it to develop small and medium-scale database-driven web applications, this is definitely a small issue that you can live with.

Final thoughts

It’s hard to believe it, but we’ve come to the end of this series. I hope that these tutorials have provided you with the help you need to start using foreign key constraints with InnoDB tables in MySQL.

Of course, I’m not saying that they’re the panacea that will solve all of the problems that may arise when working with relational databases; there are many other, additional factors that must be carefully evaluated before designing and implementing database schemas either for web sites or for web programs. Foreign key constraints are just another interesting option that may help to keep your web applications simpler to code.

Now, go ahead and give them a try!

[gp-comments width="770" linklove="off" ]

chat