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.

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!

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

Before I explain how to perform updates and deletions in cascade in the rows of a child table, I’d like to reintroduce the example developed in the preceding tutorial, It showed how to use foreign key constraints to delete only the rows of a table that stored comments on some blog posts in response to the deletion of a particular blog entry.

Having said that, here’s how the tables that comprised this basic example were initially defined:

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 depicted by the above code sample, I defined two simple InnoDB tables, where the first one is used for storing blogs post, and the second one holds all of the comments made on each of those posts. This typical example of a one-to-many relationship between a pair of tables is very convenient for demonstrating the advantages in using a single foreign key constraint in a concrete situation.

Now, say that the tables are populated with the following data:

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

Since the tables are no longer empty structures, that’s definitely a significant advance. But what if I need to remove the first (and only) entry of the “blogs” table without delegating this process to the application level? It’s really as simple as running the following statement:

DELETE FROM blogs WHERE id = 1

Thanks to the definition of a simple foreign key constraint, the previous “DELETE” command not only will delete the first blog post, but all of the comments related to it, in a single step. Pretty neat, right?

Nevertheless, as I mentioned in the introduction of this article, the InnoDB storage engine also permits you to perform both cascading updates and deletes within the definition of a particular table. Therefore, assuming that you’re interested in learning how to accomplish this in a few easy steps, in the section to come I’m going to create another example, similar to the one you saw earlier that will take advantage of this handy feature.

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

{mospagebreak title=Extending the use of foreign key constraints}

As I anticipated in the segment that you just read, it’s also possible to perform both cascading updates and deletions of records on a child table, when specific rows of its parent are removed. This helps to simplify the logic implemented by the application that handles those tables.

You’ll understand this capability provided by the InnoDB storage engine better from an example. Below I redefined the two tables that you saw before, specifying that the cascading actions must be performed on the “comments” table in response to the update and removal of the associated blog entry. In summary, here are the definitions for the tables in question:

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;

As shown above, the definition of the first “blog” table remains the same, so feel free to skip over it quickly and pay attention to the way that the second table has been created. Here things are getting more interesting.

In this case, the “comments” table also defines the same fields, with a subtle difference: this table now includes the following SQL statement:

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

Naturally, this is responsible for performing cascading updates and deletions of all of the comments that are related to a specific blog post, when these same operations are performed on that post.

Having specified that constraint for the “blog_id” foreign key, the integrity of the relationship that exists between the two previous tables is now completely handled at the database level, even though this can eventually slow down the performance of some applications.

If you’re anything like me, you want to see an example that shows how this maintenance process can be accomplished in a simple manner. Therefore, to learn how this example will be created, read the final segment of this tutorial. It’s only one click away.

{mospagebreak title=Foreign key constraints in action}

In the previous segment, I redefined the two sample IndoDB tables that are the building blocks of a fictional blog application. Now we can update and delete the comments related to a specific blog entry, whenever these tasks are performed on the entry in question.

But, that’s only in theory. To sustain it properly, it must be backed by functional code. So, suppose for a moment that the only post stored in the “blogs” table needs to be updated, along with its related comments. Well, in a case like this, this operation would be accomplished by executing only one “UPDATE” statement, like 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

As you may guess, this simple update on the first blog entry will automatically cause the updating of all of the comments associated with that post. In a similar way, if the post is deleted with the following SQL query:

DELETE FROM blogs WHERE id = 2

Then, its comments will also be removed by MySQL. Now, do you see how useful foreign key constraints can be for maintaining the consistency of relationships between a few database tables? I bet you do!

Finally, feel free to tweak all of the code samples shown in this tutorial, so you can arm yourself with a more solid background in using foreign key constraints with InnoDB tables in MySQL. The whole experience will be instructive, believe me.

Final thoughts

In this third installment of the series, I explained 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. As you saw previously, achieving this with InnoDB tables was a simple process, thanks to the use of a single foreign key constraint.

In addition, I’d like to note that all of the examples developed so far made use of plain SQL to perform operations on the sample database tables. However, in a web-based environment, a server-side language should be used to talk directly to MySQL.

Since PHP is great for doing this, in the next tutorial I’m going to show how to work with foreign key constraints along with PHP 5.

Thus, my final suggestion is simple: don’t miss the forthcoming part!

[gp-comments width="770" linklove="off" ]

antalya escort bayan antalya escort bayan Antalya escort diyarbakir escort