Home arrow PHP arrow Page 4 - Cascading Deletes of Database Rows in MySQL with Foreign Key Constraints

Deleting database rows with foreign key constraints - PHP

Foreign key constraints are a powerful feature provided by many modern RDBMS that allow you to automatically trigger specified actions, such as deletions or updates of the records of database tables that maintain a predefined relationship with each other. This is the second part of a six-part article series that shows you how to work with foreign key constraints in MySQL.

TABLE OF CONTENTS:
  1. Cascading Deletes of Database Rows in MySQL with Foreign Key Constraints
  2. Review: using foreign key constraints when updating databases
  3. Deleting database records without foreign key constraints
  4. Deleting database rows with foreign key constraints
By: Alejandro Gervasio
Rating: starstarstarstarstar / 1
December 10, 2009

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Just as you can perform cascading updates of records when using foreign key constraints, InnoDB tables also support cascading deletions, which can be extremely useful for maintaining the consistency of tables that have a specific relationship with each other.

To translate this concept to the example shown in the previous segment, I'm simply going to redefine its tables 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;

Now the “blogs” and “comments” tables that make up the data layer of this fictional blog application use the InnoDB storage engine. This means that they’re able to take advantage of foreign key constraints to produce the deletion of all the comments related to a specific blog entry, if this one is removed.

Obviously, the part of the above SQL statement that produces the cascading deletion is the following:

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

Now, thanks to the constraint imposed to the “id” field of the “blog” table, removing a blog post along with its associated comments is as simple as running only one DELETE command, like this:

DELETE FROM blogs WHERE id = 1

That was pretty easy to do, wasn’t it? Now, from this rudimentary example imagine how simple it would be to develop an application that interacts with a data layer whose tables use the power of foreign key constraints to maintain the integrity and consistency of the respective relationships at a database stage.

I recommend you try this technique when building your own database schemas so you can see for yourself how useful it can be when it comes to simplifying the logic required by your applications for handling data layers.

Finally, feel free to test and improve all of the code samples included in this tutorial, so you can get a more intimate knowledge of working with foreign key constraints in MySQL. The effort will be really worthwhile, trust me!

Final thoughts

That’s all for now. Over this second chapter of the series, you learned how to use foreign key constraints to trigger cascading deletions of records of a child table in response to the same operation performed in the parent table.

You also saw in the previous article that the same cascading effect can be produced when updating rows. So, the question that comes up is: can both deletions and updates be handled with foreign key constraints? Fortunately, the answer is yes.

The full details of this process this will be covered in the next tutorial of the series, so you don’t have any excuses to miss it!



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