Home arrow PHP arrow Page 4 - Triggering Cascading Updates and Deletions with Foreign Key Constraints

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

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.

TABLE OF CONTENTS:
  1. Triggering Cascading Updates and Deletions with Foreign Key Constraints
  2. Review: cascading updates with MySQL InnoDB tables
  3. Redefining the previous InnoDB tables
  4. Updating and deleting records in cascade within the same MySQL table
By: Alejandro Gervasio
Rating: starstarstarstarstar / 1
December 23, 2009

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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!



 
 
>>> More PHP Articles          >>> More By Alejandro Gervasio
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: