Home arrow PHP arrow Page 3 - Triggering Cascading Updates and Deletions with Foreign Key Constraints

Redefining the previous InnoDB tables - PHP

Welcome to the final installment of a series on working with foreign key constraints in MySQL. With a respectable number of code samples, this series walks you through the basics of using foreign key constraints in MySQL tables and teaches you how to use them specifically for maintaining the integrity of relational databases.

TABLE OF CONTENTS:
  1. Triggering Cascading Updates and Deletions with Foreign Key Constraints
  2. Review: cascading updates with MySQL InnoDB tables
  3. Redefining the previous InnoDB tables
  4. Updating and deleting records in cascade within the same MySQL table
By: Alejandro Gervasio
Rating: starstarstarstarstar / 1
December 23, 2009

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
 

Enabling support for cascading updates and deletions within the “comments” MySQL table that you saw before is only a matter of modifying the segment of its definition that specifies the corresponding foreign key constraint.

Nonetheless, you'll understand this process better from seeing some functional SQL code. Thus, examine the enhanced definition of the “comments” table, which now is as follows:

 

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

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

As I explained a few moments ago, the only change introduced into the definition of the “comments” table is the SQL clause that triggers both updates and deletions in cascade when the same operations are performed on the associated key in the “blogs” table. That was really simple to achieve, wasn’t it?

Now that the foreign key constraint specified within the “comments” table has been properly modified, it’s time to build a brand new script, which will use the already familiar MySQL abstraction class to perform the previously-mentioned cascading updates and removal of database rows.

To learn how this script will be developed, go ahead and read the final section of this tutorial. We’re almost finished here!



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