Working with Foreign Key Constraints in MySQL

If you have ever developed some small database-driven web applications with MySQL, then certainly you’ll know that performing CRUD operations (short for Create, Retrieve, Update, Delete) on a few relational database tables is a pretty straightforward process. In theory, a decent knowledge of the most common SQL statements, backed up by an intermediate background in the server-side scripting language of your choice, should let you do anything that you want with your MySQL tables, especially if they use the fast MyISAM database engine, right?

Well, in reality things can be more difficult, even in the simplest case. Let me explain this a bit further with a typical example. Say that you’re running a blog web site, where visitors are allowed to freely post their comments about each mind-blowing blog entry that you write every day.

In a case like this, your database schema would be comprised of at least two MyISAM tables: one that would store the pertinent blog entries, and another that would house comments for the entries in question. A one-to-many relationship between both tables should be properly established by defining a foreign key on the second table to maintain your database's integrity when updating or deleting rows.

While I’m not going to say that building an application that keeps the integrity of the two previous tables will be the most challenging task that you’ll ever have to tackle, the truth is that the integrity must be maintained at the application level. This is often the approach taken during the development of most web projects that don’t require using transactions, due to the excellent performance offered by MyISAM tables.

Of course, this comes at a cost too; as I said before, the application must take care of maintaining the integrity and consistency of the database, which naturally implies implementing more complex programming logic for adequately handling the relationships between tables. Even when database access can be simplified by using an abstraction layer and an ORM module, the more tables required by the application, the more complicated its logic will be, undoubtedly.

So, is there any way to handle foreign keys (and therefore to help maintain database integrity) at the database level with MySQL? Fortunately, the answer is a resounding yes! MySQL also supports InnoDB tables, which allow you to handle foreign key constraints in a really simple fashion. This powerful feature lets you trigger certain actions, such as updates and deletions of rows in tables that maintain a predefined relationship.

Quite possibly, the major drawback to using InnoDb tables is that they’re slightly slower than their MyISAM sisters. This is especially evident in large-scale applications that have to query many tables. But, there’s good news on the horizon, since at the time of this writing, support for foreign key constraints has been announced for MyISAM tables, too, in future releases of MySQL.

So, in this series of articles I’m going to take a closer look at using foreign key constraints with InnoDB tables. Thus you can discover (or rediscover) its numerous advantages. Also, it’s worthwhile to clarify that I’m going to use a simple PHP-based MySQL abstraction class to create some of the corresponding code samples; naturally, you can use other server-side language or even plain SQL.

Now, after this long and hopefully insightful introduction, it’s time to start using foreign key constraints with MySQL. Let’s begin right now!