Deleting database rows with foreign key constraints - PHP

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!