Home arrow PHP arrow Page 2 - Performing Record Updates with Foreign Key Constraints in MySQL

Review: updating and deleting database rows in cascade - PHP

In this fourth installment of a six-part series, I explain how to use foreign key constraints to update rows of two InnoDB tables via a basic abstraction class built into PHP 5. This example will help you start utilizing foreign key constraints with a particular server-side scripting language.

TABLE OF CONTENTS:
  1. Performing Record Updates with Foreign Key Constraints in MySQL
  2. Review: updating and deleting database rows in cascade
  3. Updating database records in cascade with PHP 5
  4. The MySQL abstraction class
By: Alejandro Gervasio
Rating: starstarstarstarstar / 3
December 17, 2009

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

In the previous chapter of this series I discussed how to utilize the power of foreign key constraints to trigger cascading updates and deletions of records of a InnoDB table that stores comments on some blog posts. In case you haven't read that part yet, below I reintroduced an example which shows how to perform these tasks in a few simple steps.

Here are the definitions of the tables that comprised the example. Take a look at them:

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 DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

To be frank, thereís nothing special about the way that the above two tables have been defined, excepting for the last one. It specifies a constraint for the ďblog_idĒ field, which triggers the cascading process when the associated rows of the ďpostĒ table are updated and deleted respectively.

It will help you understand this procedure if the tables are populated with some trivial data; a couple of INSERT SQL statements will do that in a snap:

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

Now that thereís a blog entry that has two comments associated with it, if for any reason it needs to be updated along with its comments, the process would be as simple as 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

That was easy to accomplish, wasnít it? But it's even easier is to delete the blog post in conjunction with the corresponding comments in the child table. Donít  believe me? Well, look at the following SQL statement and see for yourself:

DELETE FROM blogs WHERE id = 2

Thatís all the SQL code needed to delete the specified blog post along with its related comments, which shows how simple it is to maintain the integrity of a pair of InnoDB tables through a proper implementation of foreign key constraints.

So far, so good. Now that you've surely recalled the logic that drives the previous example, itís time to continue exploring the advantages of using those table constraints. Therefore, in accordance with the concepts deployed in the introduction, in the following section Iím going to show how to produce cascading updates on the sample tables that you saw before, but this time Iíll be using a MySQL abstraction class built in PHP 5.

Want to learn how this will be done? Then read the next section.



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