HomePHP Deleting Database Records with an Abstraction and Foreign Key Constraints
Deleting Database Records with an Abstraction and Foreign Key Constraints
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.
Maintaining the integrity of a database in MySQL or other RDBMS isnít always a simple task; this is notoriously evident where the responsibility to carry out this process is delegated to the application that interacts with the database. In those situations, establishing and maintaining relationships between the tables of a database is often performed by a combination of abstraction and ORM libraries (short for Object Relational Mapping), but the implementation of the logic required to put these two pieces to work together used to be complex.
Fortunately, most of the Relational Database Management Systems being used nowadays, including MySQL naturally, provide support for defining foreign key constraints in a straightforward fashion. This handy feature permits developers to handle and maintain relationships between tables at the database level, thus allowing them to write smaller and simpler programs using any server-side programming language.
In the previous installments of this series of articles, I demonstrated this concept specifically when working with PHP 5. In the course of those tutorials I defined two basic InnoDB tables in MySQL, which comprised the data layer of a sample blogger program that stored some simple blog posts along with the comments that were made on them.
The rudimentary nature of the program served to illustrate that, thanks to the functionality offered by foreign key constraints, updating a specified blog entry and its related comments was as easy as performing a simple UPDATE statement via an abstraction PHP 5 class, and nothing else.
The functionality provided by foreign key constraints doesnít stop here. Itís feasible to take advantage of them for deleting database rows, in a way pretty similar to performing updates. However, in this case Iím going to demonstrate how to achieve this by using the MySQL abstraction class that was coded in the previous tutorial.
Are you ready to learn how to leverage the power of foreign key constraints with MySQL and PHP 5 working together? Then start reading now!