HomePHP Page 3 - Working with Foreign Key Constraints in MySQL
Updating blog entries and maintaining database integrity - PHP
In this first part of a six-part series, I provide you with a basic introduction to using foreign key constraints with InnoDB tables in MySQL. As you'll soon see, triggering a cascading update on the records of a child table when updating the rows in a parent table is a breeze.
In the course of the previous segment, I created the two basic MyISAM tables that compose the data layer of a simple blog application, right? Certainly, this process doesn’t bear any further discussion due to its simplicity. Therefore, I'm now going to fill in those tables with some records, at least to make myself feel comfortable that my blog posts have caught some attention. Using plain SQL, the insertion of those records can be performed like this:
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')
By means of the above code sample, I’m simply simulating that my first blog post have been promptly commented by Susan and Mary, which makes me feel glad. Say that for whatever reasons, though, my entire post must be updated with another one. In a hypothetical situation, that might happen, right?
In a case like this, to maintain the consistency of the database, the “comments” table must also be updated either manually, which is not viable in practical terms, or by the application that handles the data layer. Since I’m using plain SQL for this particular example, these updates should be performed as follows:
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
UPDATE comments SET blog_id = 2 WHERE blod_id = 1
Simple and illustrative. As shown before, since the content of the first blog entry (including its ID) has been updated, this change must be reflected by the “comments” table as well. Again, it’s fair to say that in real conditions this update should be done at the application level, and not manually, which implies implementing the logic necessary to do this with any server-side language.
While achieving this, for instance with PHP, should be a straightforward procedure, the truth is that is that the entire update operation on the “comments” table could be completely delegated to the database, if foreign key constraints are used instead.
How can this be done at the present time? Well, as I expressed in the introduction, InnoDB MySQL tables support this feature seamlessly. So, in the upcoming section I’m going to rewrite the example developed earlier, this time using foreign key constraints with that type of table.
Now, go ahead and read the next few lines. I’ll be there, waiting for you.