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

Delegating update operations to the database - 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

As I explained at the end of the previous section, it’s possible to rebuild the earlier example by using foreign key constraints and InnoDB tables, instead of the default MyISAM type that you saw before. Based on this idea, the first change that must be made is redefining the two sample tables, so they can use that specific database engine.

The following SQL code does that:

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

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

From the previous code sample, it’s clear to see that the first difference is that the two tables now use the InnoDB storage engine, which supports foreign key constraints.

Other than that, you should pay close attention to the following line within the definition of the “comments” table:

CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`blog_id`) REFERENCES `blogs` (`id`) ON UPDATE CASCADE

Actually, this statement instructs MySQL to update the values of the “blog_id” foreign key on the “comments” table, when the ID of the “blogs” table is updated. In other words, what I’m doing here is delegating the maintenance of the database integrity to MySQL, meaning that when a specific blog entry is updated, its associated comments also will reflect this change immediately, instead of implementing this functionality at the application level. Pretty neat, huh?

Now that the two sample MySQL tables have been defined that way, updating both of them would be as simple as running one single “UPDATE” SQL 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 I mentioned before, there’s no need to update the “comments” table, since MySQL will take care of doing that for you automatically. In addition, it’s possible to tell MySQL not to do anything when attempting to update rows on the “blogs” table either by removing the “ON UPDATE” part of the query, or by specifying “NO ACTION” and “RESTRICT.” Other options are also available, but for now are out of the scope of this tutorial.

Now that you hopefully have a clear idea of how to work with foreign key constraints when using InnoDB tables in MySQL, I encourage you to develop your own examples to acquire a more solid background on this handy database feature.

Final thoughts

That’s all for the moment. In this first part of the series, I provided you with a basic introduction to using foreign key constraints with InnoDB tables in MySQL. As you saw by the example created earlier, triggering a “cascading” update on the records of a child table when updating rows in a parent table is a breeze, which also helps to get rid of implementing this capability inside the application that handles the data layer.

Of course, it’s also feasible to raise the same cascading effect when deleting rows on a parent table, but the full details of this process will be covered in the next tutorial. So, my suggestion is simple: don’t miss that tutorial!



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