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

Extending the use of foreign key constraints - 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

As I anticipated in the segment that you just read, it’s also possible to perform both cascading updates and deletions of records on a child table, when specific rows of its parent are removed. This helps to simplify the logic implemented by the application that handles those tables.

You'll understand this capability provided by the InnoDB storage engine better from an example. Below I redefined the two tables that you saw before, specifying that the cascading actions must be performed on the “comments” table in response to the update and removal of the associated blog entry. In summary, here are the definitions for the tables in question:

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;

As shown above, the definition of the first “blog” table remains the same, so feel free to skip over it quickly and pay attention to the way that the second table has been created. Here things are getting more interesting.

In this case, the “comments” table also defines the same fields, with a subtle difference: this table now includes the following SQL statement:

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

Naturally, this is responsible for performing cascading updates and deletions of all of the comments that are related to a specific blog post, when these same operations are performed on that post.

Having specified that constraint for the “blog_id” foreign key, the integrity of the relationship that exists between the two previous tables is now completely handled at the database level, even though this can eventually slow down the performance of some applications.

If you’re anything like me, you want to see an example that shows how this maintenance process can be accomplished in a simple manner. Therefore, to learn how this example will be created, read the final segment of this tutorial. It’s only one click away.



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