Working with Foreign Key Constraints in MySQL

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.

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!

{mospagebreak title=When to use foreign key constraints}

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.

{mospagebreak title=Updating blog entries and maintaining database integrity}

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.

{mospagebreak title=Delegating update operations to the database}

As I explained at the end of the previous section, it’s possible to rebuild the earlier example by using foreign key constraints and InnoDB tables, instead of the default MyISAM type that you saw before. Based on this idea, the first change that must be made is redefining the two sample tables, so they can use that specific database engine.

The following SQL code does that:

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=InnoDB 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`),

KEY `blog_ind` (`blog_id`),

CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`blog_id`) REFERENCES `blogs` (`id`) ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

From the previous code sample, it’s clear to see that the first difference is that the two tables now use the InnoDB storage engine, which supports foreign key constraints.

Other than that, you should pay close attention to the following line within the definition of the “comments” table:

CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`blog_id`) REFERENCES `blogs` (`id`) ON UPDATE CASCADE

Actually, this statement instructs MySQL to update the values of the “blog_id” foreign key on the “comments” table, when the ID of the “blogs” table is updated. In other words, what I’m doing here is delegating the maintenance of the database integrity to MySQL, meaning that when a specific blog entry is updated, its associated comments also will reflect this change immediately, instead of implementing this functionality at the application level. Pretty neat, huh?

Now that the two sample MySQL tables have been defined that way, updating both of them would be as simple as running one single “UPDATE” SQL 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 I mentioned before, there’s no need to update the “comments” table, since MySQL will take care of doing that for you automatically. In addition, it’s possible to tell MySQL not to do anything when attempting to update rows on the “blogs” table either by removing the “ON UPDATE” part of the query, or by specifying “NO ACTION” and “RESTRICT.” Other options are also available, but for now are out of the scope of this tutorial.

Now that you hopefully have a clear idea of how to work with foreign key constraints when using InnoDB tables in MySQL, I encourage you to develop your own examples to acquire a more solid background on this handy database feature.

Final thoughts

That’s all for the moment. In this first part of the series, I provided you with a basic introduction to using foreign key constraints with InnoDB tables in MySQL. As you saw by the example created earlier, triggering a “cascading” update on the records of a child table when updating rows in a parent table is a breeze, which also helps to get rid of implementing this capability inside the application that handles the data layer.

Of course, it’s also feasible to raise the same cascading effect when deleting rows on a parent table, but the full details of this process will be covered in the next tutorial. So, my suggestion is simple: don’t miss that tutorial!

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye