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

Cascading Deletes of Database Rows in MySQL with Foreign Key Constraints

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

The good news is that you, as a PHP developer, can take advantage of the numerous advantages offered by foreign key constraints when working with InnoDB tables in MySQL (even though support for MyISAM tables has already been announced for future releases of MySQL).

So, if youíre interested in learning the basic concepts that surround the use of foreign key constraints in MySQL, then in this set of articles youíll find an approachable guide to this topic. It's complemented with a decent variety of code samples aimed specifically at demonstrating how to maintain the integrity of a database when updating and deleting the rows of a parent table.

If you already read the article that precedes this one, then itís highly probable that you now have a pretty clear idea of how to work with foreign key constraints when using InnoDB tables in MySQL. In that introductory part of the series, I explained how to trigger a cascading update operation on the records of a child table when the rows of the parent table were updated as well.

While describing this cascading updating process in plain English may sound a bit confusing and ambiguous, the truth is that translating it to functional SQL code is much easier than you might think. In its simplest version, the procedure is reduced to first creating a parent and a child table, defining a foreign key in the child, and then specifying what action will take place when updating the parentís records. Period.

Undeniably, the major benefit to using foreign key constraints is that relationships between tables can be easily handled at the database level, instead of implementing the required logic within the application that interacts with the data layer. It's worth noting that this may be a detrimental factor in terms of performance, particularly when building large-scale programs.

Since database performance issues are certainly out of the scope of this article series, what Iím going to do in the next few lines will consist of demonstrating how to use foreign key constraints for triggering cascading deletions of records in a child table, where rows in the parent table are deleted too.

Want to see how this mix of buzzwords can be turned into clean SQL code? Then donít hesitate anymore; start reading now!



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