Home arrow PHP arrow Page 3 - Working with Foreign Key Constraints in MySQL

Updating blog entries and maintaining database integrity - PHP

In this first part of a six-part series, I provide you with a basic introduction to using foreign key constraints with InnoDB tables in MySQL. As you'll soon see, triggering a cascading update on the records of a child table when updating the rows in a parent table is a breeze.

TABLE OF CONTENTS:
  1. Working with Foreign Key Constraints in MySQL
  2. When to use foreign key constraints
  3. Updating blog entries and maintaining database integrity
  4. Delegating update operations to the database
By: Alejandro Gervasio
Rating: starstarstarstarstar / 8
December 09, 2009

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

In the course of the previous segment, I created the two basic MyISAM tables that compose the data layer of a simple blog application, right? Certainly, this process doesn’t bear any further discussion due to its simplicity. Therefore, I'm now going to fill in those tables with some records, at least to make myself feel comfortable that my blog posts have caught some attention. Using plain SQL, the insertion of those records can be performed like this:

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')

By means of the above code sample, I’m simply simulating that my first blog post have been promptly commented by Susan and Mary, which makes me feel glad. Say that for whatever reasons, though, my entire post must be updated with another one. In a hypothetical situation, that might happen, right?

In a case like this, to maintain the consistency of the database, the “comments” table must also be updated either manually, which is not viable in practical terms, or by the application that handles the data layer. Since I’m using plain SQL for this particular example, these updates should be performed as follows:

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

 

UPDATE comments SET blog_id = 2 WHERE blod_id = 1

Simple and illustrative. As shown before, since the content of the first blog entry (including its ID) has been updated, this change must be reflected by the “comments” table as well. Again, it’s fair to say that in real conditions this update should be done at the application level, and not manually, which implies implementing the logic necessary to do this with any server-side language.

While achieving this, for instance with PHP, should be a straightforward procedure, the truth is that is that the entire update operation on the “comments” table could be completely delegated to the database, if foreign key constraints are used instead.

How can this be done at the present time? Well, as I expressed in the introduction, InnoDB MySQL tables support this feature seamlessly. So, in the upcoming section I’m going to rewrite the example developed earlier, this time using foreign key constraints with that type of table.

Now, go ahead and read the next few lines. I’ll be there, waiting for you.



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