Cascading Deletes of Database Rows in MySQL with Foreign Key Constraints

Foreign key constraints are a powerful feature provided by many modern RDBMS that allow you to automatically trigger specified actions, such as deletions or updates of the records of database tables that maintain a predefined relationship with each other. This is the second part of a six-part article series that shows you how to work with foreign key constraints in MySQL.

The good news is that you, as a PHP developer, can take advantage of the numerous advantages offered by foreign key constraints when working with InnoDB tables in MySQL (even though support for MyISAM tables has already been announced for future releases of MySQL).

So, if you’re interested in learning the basic concepts that surround the use of foreign key constraints in MySQL, then in this set of articles you’ll find an approachable guide to this topic. It’s complemented with a decent variety of code samples aimed specifically at demonstrating how to maintain the integrity of a database when updating and deleting the rows of a parent table.

If you already read the article that precedes this one, then it’s highly probable that you now have a pretty clear idea of how to work with foreign key constraints when using InnoDB tables in MySQL. In that introductory part of the series, I explained how to trigger a cascading update operation on the records of a child table when the rows of the parent table were updated as well.

While describing this cascading updating process in plain English may sound a bit confusing and ambiguous, the truth is that translating it to functional SQL code is much easier than you might think. In its simplest version, the procedure is reduced to first creating a parent and a child table, defining a foreign key in the child, and then specifying what action will take place when updating the parent’s records. Period.

Undeniably, the major benefit to using foreign key constraints is that relationships between tables can be easily handled at the database level, instead of implementing the required logic within the application that interacts with the data layer. It’s worth noting that this may be a detrimental factor in terms of performance, particularly when building large-scale programs.

Since database performance issues are certainly out of the scope of this article series, what I’m going to do in the next few lines will consist of demonstrating how to use foreign key constraints for triggering cascading deletions of records in a child table, where rows in the parent table are deleted too.

Want to see how this mix of buzzwords can be turned into clean SQL code? Then don’t hesitate anymore; start reading now!

{mospagebreak title=Review: using foreign key constraints when updating databases}

As I mentioned, the previous article of this series discussed how to utilize foreign key constraints to maintain the relationship between two sample InnoDB tables. The first one stored some simple blog entries, and the second one housed the comments made to those entries.

The neat part of this example is that defining a foreign key constraint on the child table allowed us to automatically delete all of the comments related to a specific blog post when this post was removed. You’ll understand this better from the example, so here are the respective definitions of the mentioned tables, which establish a one-to-many relationship with each other:

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;

Apart from defining some simple fields on the two above InnoDB tables, the previous code sample uses a foreign key constraint to allow a cascading update of the rows of the “comments” table whenever the “id” key of the parent is updated too.

The line that defines the constraint on the “id” parent field is the following:

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

Aside from specifying a cascading update on the child table based on the operations performed on the parent, the InnoDB engine supports other actions. These include “NO ACTION” and “RESTRICT,” which will refuse any update or deletion on the parent table.

Now, returning to the previous example, and based on the definition of the sample MySQL tables, if they were 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’)

Then, if for any reason it’s necessary to update the first blog entry, automatically all of its related comments will be updated also, simply by running the following SQL statement:

"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 pretty neat, right? As I explained before, foreign key constraints permit you to delegate the maintenance of relationships between tables at the database level, which means writing less code for the application that interacts with the data layer.

In addition, it’s important to note that it’s possible to trigger deletions in a cascade, in a way similar to the case illustrated a moment ago. Thus, in the following section I’m going to reuse the two sample tables defined earlier to demonstrate how useful a foreign key constraint can be for deleting the comments related to a particular blog post when this entry is removed.

To see how this brand new example will be developed, click on the link shown below and keep reading.

{mospagebreak title=Deleting database records without foreign key constraints}

To prove how helpful foreign key constraints can be for maintaining the integrity of a database when rows of a parent table are deleted, I’m going to recreate the example that you learned in the previous section, but this time using MyISAM tables.

So, first I’m going to define the tables, a basic process accomplished by the following SQL code:

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=MyISAM 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`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

So far, there’s nothing special with reference to the way that the two sample tables have been defined. However, you must notice that they use the default MyISAM database engine, therefore foreign key constraints won’t be supported (at least at the time of this writing).

Having defined the tables that comprise the data layer of a basic blog application, it’s time to populate them with some fictional data. Here’s the SQL code that does that:

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

Essentially, what the above code snippet does is insert a blog post in the “blogs” table, and two comments in the child table, thus simulating that the sample blog application is up and running. Now, if my first post was deleted, then its related comments should be removed as well.

But how could this be done? Well, the following SQL statements would do the task pretty roughly:

DELETE FROM blogs WHERE id = 1

DELETE FROM comments WHERE blog_id = 1

Naturally, in a real-world scenario, these two DELETE statements should be performed by a server-side language like PHP, Python or Ruby, and not using raw SQL, but for example purposes, these are good enough.

So far, so good. At this point I’m certain that you grasped the logic involved in deleting a blog entry, along with its associated comments, when using MyISAM tables. So, next I’ll rebuild the same example, but this time the tables will be defined by utilizing the InnoDB storage engine and a simple foreign key constraint.

This example will be set up in the last section of this tutorial. Therefore, jump ahead and read the next few lines.

{mospagebreak title=Deleting database rows with foreign key constraints}

Just as you can perform cascading updates of records when using foreign key constraints, InnoDB tables also support cascading deletions, which can be extremely useful for maintaining the consistency of tables that have a specific relationship with each other.

To translate this concept to the example shown in the previous segment, I’m simply going to redefine its tables 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;

Now the “blogs” and “comments” tables that make up the data layer of this fictional blog application use the InnoDB storage engine. This means that they’re able to take advantage of foreign key constraints to produce the deletion of all the comments related to a specific blog entry, if this one is removed.

Obviously, the part of the above SQL statement that produces the cascading deletion is the following:

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

Now, thanks to the constraint imposed to the “id” field of the “blog” table, removing a blog post along with its associated comments is as simple as running only one DELETE command, like this:

DELETE FROM blogs WHERE id = 1

That was pretty easy to do, wasn’t it? Now, from this rudimentary example imagine how simple it would be to develop an application that interacts with a data layer whose tables use the power of foreign key constraints to maintain the integrity and consistency of the respective relationships at a database stage.

I recommend you try this technique when building your own database schemas so you can see for yourself how useful it can be when it comes to simplifying the logic required by your applications for handling data layers.

Finally, feel free to test and improve all of the code samples included in this tutorial, so you can get a more intimate knowledge of working with foreign key constraints in MySQL. The effort will be really worthwhile, trust me!

Final thoughts

That’s all for now. Over this second chapter of the series, you learned how to use foreign key constraints to trigger cascading deletions of records of a child table in response to the same operation performed in the parent table.

You also saw in the previous article that the same cascading effect can be produced when updating rows. So, the question that comes up is: can both deletions and updates be handled with foreign key constraints? Fortunately, the answer is yes.

The full details of this process this will be covered in the next tutorial of the series, so you don’t have any excuses to miss it!

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

antalya escort bayan antalya escort bayan Antalya escort diyarbakir escort