When to use foreign key constraints - PHP

Frankly speaking, it’s not mandatory to use foreign key constraints when working with InnoDB tables in MySQL. However, to demonstrate how useful they can be in certain cases, I’m going to translate to functional code the example mentioned at the beginning. It involves two MyISAM tables which will be used for storing some blog entries and the comments on them respectively.

With that database schema defined, I’m going to set up a one-to-many relationship between these tables by creating a foreign key on the table that houses the comments, thus tying them to a particular blog entry.

Having explained that, here’s the basic SQL code that roughly creates the sample MyISAM tables:

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;

Well, at this point things are starting to look a bit more interesting. I’ve just defined the two MyISAM tables that will comprise the data layers of the blog application that I plan to build progressively.

As you can see above, the first table, called “blogs,” is made up of a few intuitive fields that will be used for storing the ID of each blog entry, then its title and content, and finally the author that posted it. On the other hand, the “comments” table will hold the comments on each entry, and will use the entry’s ID as its foreign key, in this way setting up the aforementioned one-to-many relationship.

So far, nothing special is happening here, since creating two simple MyISAM tables like the ones shown previously should be pretty easy to grasp for you. So, next we'll populate those tables with some trivial records, so you can see what operations need to be performed on the “comments” table when one blog entry is deleted in the other one.

To learn the full details of this process, click on the link below and read the following section, please.