Home arrow PHP arrow Page 3 - Deleting Database Records with an Abstraction and Foreign Key Constraints

Enabling support for cascading row deletions - PHP

In this fifth article of a six-part series, you will learn how to use foreign key constraints in MySQL to produce the deletion in cascade of rows in a child table when the related rows of the corresponding parent are deleted as well. We'll use a simple MySQL abstraction class to do the trick.

TABLE OF CONTENTS:
  1. Deleting Database Records with an Abstraction and Foreign Key Constraints
  2. Review: updating database rows in cascade through a MySQL abstraction class
  3. Enabling support for cascading row deletions
  4. The MySQL abstraction class in action
By: Alejandro Gervasio
Rating: starstarstarstarstar / 1
December 21, 2009

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

In the earlier section, you recalled how to trigger cascading updates of records of a child table when rows in the associated parent were updated. The next step involves producing a similar effect, but this time with record deletions.

Again, itís worthwhile to mention that a similar example was coded in a previous tutorial using raw SQL, but in this case Iím going to use the PHP 5-based abstraction class that was shown a few moments ago.

That being clarified, itís time to redefine the pertinent blog tables, which will be created in the following way:

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

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

As shown before, the definitions of the InnoDB tables that compose the storage mechanism of the blogger program remain nearly the same, with the exception of the following line:

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

Obviously, this part of the tableís definition specifies the deletion in cascade of the comments related to a particular blog entry, when the entry is removed. This permits you to maintain the integrity of the relationship between the tables.

Now that these tables have been properly modified, whatís the next step? Well, since cascading deletions of database rows are usually triggered by a server-side language, in the last segment of this tutorial Iím going to explain how to perform this task with the MySQL abstraction class was shown previously.

So, go ahead and read the following segment. 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: