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

Updating and Deleting Records in MySQL Tables with Foreign Key Constraints

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

True to form, developing database-driven applications that can maintain the integrity of multiple tables isnít an easy task. This applies to programs that interact with the most popular open source RDBMS available today, MySQL server.

Understanding why building such applications can be a pretty difficult process is actually a matter of common sense rather than dealing with the complexities of an esoteric principle of software engineering. If an application must handle a bunch of database tables that keep a defined relationship with each other, problems are just around the corner, and can arise as soon as different records are updated or deleted in a parent table, since these changes must be properly reflected by its children.

Speaking specifically of MySQL, in most cases these database integrity issues can be solved by using a well-trusted ORM library, but fortunately this isnít the only option. As you may have heard, itís possible to use the InnoDB storage engine with MySQL, which supports the definition of foreign key constraints. Using this engine means you can perform specified actions on child tables in response to certain operations, such as updates and deletions, executed on a parent table.

In the previous article of this series I demonstrated how useful foreign key constraints can be. Specifically, I showed how to trigger cascading deletions of records of a table storing blog post comments when its associated post was removed from a parent table.

Regardless of the rudimentary nature of this introductory example, it came in handy for illustrating how to maintain the integrity of a couple of relational tables at the database level, instead of delegating this responsibility to the application that handles the data layer.

Iím only scratching the surface of the advantages in using foreign key constraints with InnoDB tables in MySQL. It's also feasible to trigger both cascading updates and deletions of rows of a specified child table when performing these operations on a key of a parent table, thus making it even easier to keep the consistency of a database.

Therefore, in this third chapter of the series Iím going to explore this feature in depth, so you can start using it within your own MySQL-driven applications with minor efforts. Letís get started right 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: