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

Review: deleting database rows in cascade - 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

Before I explain how to perform updates and deletions in cascade in the rows of a child table, I’d like to reintroduce the example developed in the preceding tutorial, It showed how to use foreign key constraints to delete only the rows of a table that stored comments on some blog posts in response to the deletion of a particular blog entry.

Having said that, here’s how the tables that comprised this basic example were initially defined:

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 depicted by the above code sample, I defined two simple InnoDB tables, where the first one is used for storing blogs post, and the second one holds all of the comments made on each of those posts. This typical example of a one-to-many relationship between a pair of tables is very convenient for demonstrating the advantages in using a single foreign key constraint in a concrete situation.

Now, say that the tables are populated with the following data:

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

Since the tables are no longer empty structures, that’s definitely a significant advance. But what if I need to remove the first (and only) entry of the “blogs” table without delegating this process to the application level? It’s really as simple as running the following statement:

DELETE FROM blogs WHERE id = 1

Thanks to the definition of a simple foreign key constraint, the previous “DELETE” command not only will delete the first blog post, but all of the comments related to it, in a single step. Pretty neat, right?

Nevertheless, as I mentioned in the introduction of this article, the InnoDB storage engine also permits you to perform both cascading updates and deletes within the definition of a particular table. Therefore, assuming that you’re interested in learning how to accomplish this in a few easy steps, in the section to come I’m going to create another example, similar to the one you saw earlier that will take advantage of this handy feature.

Now, click on the link that appears below and keep reading.



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