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!

{mospagebreak title=Review: updating database rows in cascade through a MySQL abstraction class}

As you probably remember, the previous tutorial explained how to use foreign key constraints to perform cascading updates on the rows of the tables that comprised a sample blog application. Below I reintroduced the definitions for those tables, along with the MySQL abstraction class built with PHP 5, responsible for interacting with them.

So first we’ll take a look at the way the sample tables were created. Here they are:

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 UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Aside from defining a few basic fields in both MySQL tables for storing some blog posts and the comments that could be made on them, the second table defines a foreign key constraint that will automatically update those comments if the ID of its parent blog entry is also updated.

Nonetheless, as I said in the introduction, the entire updating process will be performed via a simple MySQL abstraction class created in PHP 5, whose definition looked like this:

class MySQL

{

private $result = NULL;

private $link = NULL;

 

// connect to MySQL

public function __construct($host, $user, $password, $database)

{

if (FALSE === ($this->link = mysqli_connect($host, $user, $password, $database)))

{

throw new Exception(‘Error : ‘ . mysqli_connect_error());

}

}

 

// perform query

public function query($query)

{

if (is_string($query) AND empty($query) === FALSE)

{

if (FALSE === ($this->result = mysqli_query($this->link, $query)))

{

throw new Exception(‘Error performing query ‘ . $query . ‘ Error message :’ .mysqli_error($this->link));

}

}

}

 

// fetch row from result set

public function fetch()

{

if (FALSE === ($row = mysqli_fetch_object($this->result)))

{

mysqli_free_result($this->result);

return FALSE;

}

return $row;

}

 

// get insertion ID

public function getInsertID()

{

return mysqli_insert_id($this->link);

}

// count rows in result set

public function countRows()

{

if ($this->result !== NULL)

{

return mysqli_num_rows($this->result);

}

}

// implement destructor to close the database connection

function __destruct()

{

mysqli_close($this->link);

}

}

Now, things are becoming a bit more interesting, right? Suppose for a moment that an instance of the above “MySQL” class is used to insert a blog entry into the corresponding “blogs” table, along with a couple of comments into the other table. This would be done through the following code segment:

require_once ‘mysqlclass.php';

$db = new MySQL(‘host’, ‘user’, ‘password’, ‘test’);

// insert new entry into ‘blogs’ database table

$db->query("INSERT INTO blogs (id, title, content, author) VALUES (NULL,’Title of the first blog entry’, ‘Content of the first blog entry’, ‘Alejandro Gervasio’)");

 

$insid = $db->getInsertID();

// insert new comments into ‘comments’ database table

$db->query("INSERT INTO comments (id, blog_id, comment, author) VALUES (NULL, $insid, ‘Commenting first blog entry’, ‘Susan Norton’), (NULL, $insid, ‘Commenting first blog entry’, ‘Mary Wilson’)");

So far, nothing unexpected is going on here. But, bear with me for a moment and let me show you what happens when the first blog post is updated with the following data:

// update row in ‘blogs’ (rows in ‘comments’ table are updated automatically)

$db->query("UPDATE blogs SET id = 2, title = ‘Title of the first blog entry’, content = ‘Content of the first blog entry’, author = ‘John Doe’ WHERE id = ‘$insid’");

Well, here’s where the magic of using foreign key constraints takes place. When the ID corresponding to the first blog entry is updated, its related comments will be updated as well. If you’re not convinced of this, try out the above example on your own web server and see how well it works.

Now that you’ve grasped the underlying logic of the previous code sample, it’s time to explore other advantages to using foreign key constraints with InnoDB tables in MySQL. So, as I mentioned at the beginning of this article, foreign key constraints can be used to produce a similar cascading effect in a child table, when associated rows of a parent table are deleted.

As you may recall, this situation was represented in a previous article by using plain SQL, but it would be useful to recreate it by utilizing the MySQL abstraction class coded before. So, in the next section I’m going to redefine the two InnoDB tables that make up the data layer of this blog program, so they can seamlessly support the deletion of rows in cascade.

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

{mospagebreak title=Enabling support for cascading row deletions}

In the earlier section, you recalled how to trigger cascading updates of records of a child table when rows in the associated parent were updated. The next step involves producing a similar effect, but this time with record deletions.

Again, it’s worthwhile to mention that a similar example was coded in a previous tutorial using raw SQL, but in this case I’m going to use the PHP 5-based abstraction class that was shown a few moments ago.

That being clarified, it’s time to redefine the pertinent blog tables, which will be created 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;

As shown before, the definitions of the InnoDB tables that compose the storage mechanism of the blogger program remain nearly the same, with the exception of the following line:

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

Obviously, this part of the table’s definition specifies the deletion in cascade of the comments related to a particular blog entry, when the entry is removed. This permits you to maintain the integrity of the relationship between the tables.

Now that these tables have been properly modified, what’s the next step? Well, since cascading deletions of database rows are usually triggered by a server-side language, in the last segment of this tutorial I’m going to explain how to perform this task with the MySQL abstraction class was shown previously.

So, go ahead and read the following segment. It’s only one click away.

{mospagebreak title=The MySQL abstraction class in action}

In the previous section I redefined one of the two sample InnoDB tables (the one that stores comments) to enable the support for deletion in cascade of its rows. So the only thing that remains undone is building a script that first populates those tables with some fictional data, and then removes a particular blog post and its corresponding comments in one single step. First I’m going to save the earlier “MySQL” class to a file called “mysqlclass.php,” which will look like this:

<?php

 

class MySQL

{

private $result = NULL;

private $link = NULL;

 

// connect to MySQL

public function __construct($host, $user, $password, $database)

{

if (FALSE === ($this->link = mysqli_connect($host, $user, $password, $database)))

{

throw new Exception(‘Error : ‘ . mysqli_connect_error());

}

}

 

// perform query

public function query($query)

{

if (is_string($query) AND empty($query) === FALSE)

{

if (FALSE === ($this->result = mysqli_query($this->link, $query)))

{

throw new Exception(‘Error performing query ‘ . $query . ‘ Error message :’ .mysqli_error($this->link));

}

}

}

 

// fetch row from result set

public function fetch()

{

if (FALSE === ($row = mysqli_fetch_object($this->result)))

{

mysqli_free_result($this->result);

return FALSE;

}

return $row;

}

 

// get insertion ID

public function getInsertID()

{

return mysqli_insert_id($this->link);

}

// count rows in result set

public function countRows()

{

if ($this->result !== NULL)

{

return mysqli_num_rows($this->result);

}

}

// implement destructor to close the database connection

function __destruct()

{

mysqli_close($this->link);

}

}

 

?>

Now, moving forward, here’s the script that fills the tables with some data and  deletes the first (and only) blog post, along with all of its comments:

require_once ‘mysqlclass.php';

$db = new MySQL(‘host’, ‘user’, ‘password’, ‘test’);

// insert new entry into ‘blogs’ database table

$db->query("INSERT INTO blogs (id, title, content, author) VALUES (NULL,’Title of the first blog entry’, ‘Content of the first blog entry’, ‘Alejandro Gervasio’)");

 

$insid = $db->getInsertID();

// insert new comments into ‘comments’ database table

$db->query("INSERT INTO comments (id, blog_id, comment, author) VALUES (NULL, $insid, ‘Commenting first blog entry’, ‘Susan Norton’), (NULL, $insid, ‘Commenting first blog entry’, ‘Mary Wilson’)");

 

// delete row in ‘blogs’ table; rows in ‘comments’ table are deleted automatically

 

$db->query("DELETE FROM blogs WHERE id = ‘$insid’");

There you have it. As the above code fragments show, it’s ridiculously easy (if the phrase is applicable) to maintain the consistency of the relationship established between the “blogs” and the “comments” tables, even when they’re accessed via a basic MySQL abstraction class.

Also, aside from testing the previous example, I encourage you to develop your own scripts, either by using PHP 5 or the programming language of your choice, so you can see the actual power of using foreign key constraints with MySQL.

Final thoughts

In this fifth episode of the series, you learned how to take advantage of 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. And best of all, this was carried out with a simple MySQL abstraction class. What more can you ask for?  

Well, actually you can ask for much more, and hopefully you won’t be disappointed; in the last tutorial I’m going to explain how to perform cascading updates and deletions in the same InnoDB table, using the aforementioned MySQL class.

Don’t miss the last article!

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan