Deleting database records without foreign key constraints - PHP

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.