Foreign key constraints in action - PHP

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!