Performing Record Updates with Foreign Key Constraints in MySQL

In this fourth installment of a six-part series, I explain how to use foreign key constraints to update rows of two InnoDB tables via a basic abstraction class built into PHP 5. This example will help you start utilizing foreign key constraints with a particular server-side scripting language.

Being the most popular open source RDBMS available nowadays, it’s not breaking news to say that MySQL supports multiple storage engines, including the default, highly-fast MyISAM type, which you may have used hundreds of times before for developing your own database-driven web applications.

However, there are times when it’s necessary to look at other MySQL storage engines. Sometimes a particular project needs to implement additional features, like handling foreign key constraints. In a case like this, InnoDB tables will fit this requirement very efficiently, even though they may be a bit slower in terms of performance than their MyISAM counterparts.

Undeniably, one of the major advantages to using foreign key constraints with InnoDB tables is that this capability allows you to handle and maintain relationships between several tables at the database level, instead of delegating this task to certain modules or libraries of the application responsible for interacting with the tables in question.

Naturally, if you’ve been a patient follower of this series of articles and already went through all of the articles that precede this one, then you have a solid background in working with foreign key constraints within IndoDB tables. In those tutorials I explained how to trigger cascading updates and deletions on the records of a child table, when performing the same operations on rows of its related parent table.

Those basic tables initially comprised the data layer of a fictional blog application, but as you might have guessed, foreign key constraints can be used in a variety of programs with great results. In addition, it’s valid to recall that all of the examples developed so far were coded by utilizing raw SQL, without tying them to a particular programming language.

Taking into account that MySQL is very often strongly coupled with PHP, in this fourth part of the series I’m going to demonstrate how to work with foreign key constraints within a pair of InnoDB tables that will be accessed with PHP 5. Doing so should give you an accurate idea of how to use this feature in a more realistic scenario.

Now it’s time to leave the preliminaries behind and learn how to leverage the power of foreign key constraints with PHP 5. Let’s get started!

{mospagebreak title=Review: updating and deleting database rows in cascade}

In the previous chapter of this series I discussed how to utilize the power of foreign key constraints to trigger cascading updates and deletions of records of a InnoDB table that stores comments on some blog posts. In case you haven’t read that part yet, below I reintroduced an example which shows how to perform these tasks in a few simple steps.

Here are the definitions of the tables that comprised the example. Take a look at them:

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

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

To be frank, there’s nothing special about the way that the above two tables have been defined, excepting for the last one. It specifies a constraint for the “blog_id” field, which triggers the cascading process when the associated rows of the “post” table are updated and deleted respectively.

It will help you understand this procedure if the tables are populated with some trivial data; a couple of INSERT SQL statements will do that in a snap:

INSERT INTO blogs (id, title, content, author) VALUES (NULL,’Title of the first blog entry’, ‘Content of the first blog entry’, ‘Alejandro Gervasio’)

 

INSERT INTO comments (id, blog_id, comment, author) VALUES (NULL, 1, ‘Commenting first blog entry’, ‘Susan Norton’), (NULL, 1, ‘Commenting first blog entry’, ‘Mary Wilson’)

Now that there’s a blog entry that has two comments associated with it, if for any reason it needs to be updated along with its comments, the process would be as simple as this:

UPDATE blogs SET id = 2, title = ‘Title of the first blog entry’, content = ‘Content of the first blog entry’, author = ‘John Doe’ WHERE id = 1

That was easy to accomplish, wasn’t it? But it’s even easier is to delete the blog post in conjunction with the corresponding comments in the child table. Don’t  believe me? Well, look at the following SQL statement and see for yourself:

DELETE FROM blogs WHERE id = 2

That’s all the SQL code needed to delete the specified blog post along with its related comments, which shows how simple it is to maintain the integrity of a pair of InnoDB tables through a proper implementation of foreign key constraints.

So far, so good. Now that you’ve surely recalled the logic that drives the previous example, it’s time to continue exploring the advantages of using those table constraints. Therefore, in accordance with the concepts deployed in the introduction, in the following section I’m going to show how to produce cascading updates on the sample tables that you saw before, but this time I’ll be using a MySQL abstraction class built in PHP 5.

Want to learn how this will be done? Then read the next section.

{mospagebreak title=Updating database records in cascade with PHP 5}

As I expressed in the introduction, it’d be instructive to demonstrate how to update in cascade the comments posted on a particular blog entry by using a popular server-side scripting language like PHP 5. To do so, it’s necessary to build a piece of code that allows us to access the InnoDB tables shown in the previous segment, which in this particular case will be a basic MySQL abstraction class built in PHP 5.

Having explained that, here’s the complete source code corresponding to this brand MySQL-accessing class. Look at it, please:

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);

}

}

 

As shown above, the MySQL abstraction class that I just defined is very simple to follow. It presents some useful methods for performing queries, counting rows in a result set and getting insertion IDs. Quite possibly the most important facet to note is that the class internally uses the “mysqli” PHP extension to interface with MySQL, but other than that detail, the class’s code should be easy for you to understand.

Well, now that there’s a PHP 5 class that can be used for interacting with MySQL databases, we need to use its API for executing cascaded updates on the InnoDB tables defined in the previous section.

This will be the perfect conclusion to this tutorial, so to see how the “MySQL” class will be utilized in conjunction with the tables, read the following section.

{mospagebreak title=The MySQL abstraction class in action}

At this point, everything has been properly set up to demonstrate how to use the previous MySQL class to perform cascaded updates of records on the tables shown in the first section of this article, right? Well, not quite; it’s necessary to redefine the tables so they can perform only these updating operations. 

So, with that requirement in mind, here are the definitions of these tables, which  comprise the data layer of a simple blog program:

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;

Now that the above tables have being related by specifying a foreign key constraint for the “blog_id” field of the last one, it’s time to use the earlier MySQL abstraction class to populate them with some sample data. Assuming that the class has been coded separately on a file called “mysqlclass.php,” the following script will insert a single blog post into the “blog” table and a couple of comments into the “comments” table:

require_once ‘mysqlclass.php';

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

// insert new entry in ‘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 in ‘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’)");

Certainly, I have to admit that the MySQL class does a decent job of abstracting database accesses, even though the corresponding SQL queries must still be coded manually. On the other hand this is propitious, since it permits me to show how to use the class to update the comments related to the first blog entry, when this entry is updated.

The code snippet that performs this cascading operation is the following:

// 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 = 1");

Even though I added an additional abstraction class to interact with the two previous InnoDB tables, the SQL code required to trigger the cascading updates remains extremely simple. This demonstrates that the integrity of the relationship between those tables is always maintained at the database level, and not by the PHP 5 application.

Finally, with this example I’m finishing the fourth tutorial of this series. As always, feel free to edit all of the code samples included in this article, so you can sharpen your skills in using foreign key constraints in MySQL.

Final thoughts

Over this fourth installment of the series, I explained how to use foreign key constraints to update rows of two InnoDB tables via a basic abstraction class built in PHP 5. Hopefully, this example will provide you with the right information to start utilizing foreign key constraints with a particular server-side scripting language.

Now, in the upcoming tutorial I’m going to discuss how to use those constraints to delete the rows of the sample MySQL tables that you saw before.

Don’t miss the next part!

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