Getting started with foreign keys

An often asked question is how to deal with relations in databases. Joomlatools Framework doesn't support relations out of the box yet, but fortunately the InnoDB engine in MySQL does!

There are three important rules that must be met in order to create relations:

1. Both tables must be InnoDB tables and must not be TEMPORARY tables.
2. Keys must have similar internal data types.
3. Both foreign and referenced keys need to be indexed.

A basic example

Let's see the basics through an example:

Assume that we have a component foobar with two database tables: foobar_articles and foobar_authors.

Both tables have the following columns:

• foobar_articles: foobar_article_id and title
• foobar_authors: foobar_author_id and name

Here is the SQL code to create the tables:

CREATE TABLE IF NOT EXISTS `foobar_articles` (
  `foobar_article_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`foobar_article_id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
CREATE TABLE IF NOT EXISTS `foobar_authors` (
  `foobar_author_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`foobar_author_id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;

Many-to-many relation

An author can belong to more than one article, and an article can belong to more than one author. So this is a many-to-many relation. Therefor need a relation table as too, which has foobar_article_id and foobar_author_id columns:

CREATE TABLE IF NOT EXISTS `foobar_articles_authors` (
  `foobar_article_id` INT UNSIGNED NOT NULL,
  `foobar_author_id` INT UNSIGNED NOT NULL,
  FOREIGN KEY (`foobar_article_id`) REFERENCES `foobar_articles`(`foobar_article_id`) ON DELETE CASCADE,
  FOREIGN KEY (`foobar_author_id`) REFERENCES `foobar_authors`(`foobar_author_id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;

As you can see, we specified the foreign keys with CASCADE and RESTRICT options.

Let’s try to delete an article. Notice that relations in the foobar_articles_authors table are deleted automatically. Now let’s try to remove an author. If the author is referenced in the relation table, then MySQL refuses to delete it. This ensures referential integrity.

Keeping referential integrity

By using InnoDB, you can prevent accidental removal of referenced rows. Other than its robust data management features, it has usability advantages since some database management tools support foreign keys.

For example in Sequel Pro, an arrow is displayed next to the column’s value, and it takes you to the referenced row when you click on it. This little feature can be useful many times.

InnoDB has become the default engine in MySQL 5.5 and has many features over MyISAM. I am highly recommending every developer to consider using it in their applications.

This was just a basic introduction to foreign keys, it is capable of much more! If you are interested, visit the MySQL Reference Manual.