Home arrow PHP arrow Page 4 - Updating and Deleting Records in MySQL Tables with Foreign Key Constraints

Foreign key constraints in action - PHP

In this third installment of a six-part series, I explain how to trigger cascading updates and deletions of records of a child table, when performing the same operations on the associated rows of its parent table. Achieving this with InnoDB tables is a simple process, thanks to the use of a single foreign key constraint.

TABLE OF CONTENTS:
  1. Updating and Deleting Records in MySQL Tables with Foreign Key Constraints
  2. Review: deleting database rows in cascade
  3. Extending the use of foreign key constraints
  4. Foreign key constraints in action
By: Alejandro Gervasio
Rating: starstarstarstarstar / 3
December 16, 2009

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

In the previous segment, I redefined the two sample IndoDB tables that are the building blocks of a fictional blog application. Now we can update and delete the comments related to a specific blog entry, whenever these tasks are performed on the entry in question.

But, that’s only in theory. To sustain it properly, it must be backed by functional code. So, suppose for a moment that the only post stored in the “blogs” table needs to be updated, along with its related comments. Well, in a case like this, this operation would be accomplished by executing only one “UPDATE” 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 you may guess, this simple update on the first blog entry will automatically cause the updating of all of the comments associated with that post. In a similar way, if the post is deleted with the following SQL query:

DELETE FROM blogs WHERE id = 2

Then, its comments will also be removed by MySQL. Now, do you see how useful foreign key constraints can be for maintaining the consistency of relationships between a few database tables? I bet you do!

Finally, feel free to tweak all of the code samples shown in this tutorial, so you can arm yourself with a more solid background in using foreign key constraints with InnoDB tables in MySQL. The whole experience will be instructive, believe me.

Final thoughts

In this third installment of the series, I explained how to trigger cascading updates and deletions of records of a child table when performing the same operations on the associated rows of its parent table. As you saw previously, achieving this with InnoDB tables was a simple process, thanks to the use of a single foreign key constraint.

In addition, I’d like to note that all of the examples developed so far made use of plain SQL to perform operations on the sample database tables. However, in a web-based environment, a server-side language should be used to talk directly to MySQL.

Since PHP is great for doing this, in the next tutorial I’m going to show how to work with foreign key constraints along with PHP 5.

Thus, my final suggestion is simple: don’t miss the forthcoming part!



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