Updating and deleting records in cascade within the same MySQL table - PHP

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!