Kevin Bradwick

Foreign Key tutorial using MySQL

Foreign key’s in a database table reference primary key’s of rows in another table. Why does this matter? Well, if you have ever built an application whereby updating/deleting a row from one table requires further transactions to be carried out on other, you’ll know the nuisance and confusion this can cause (especially if your application is reasonably large). Foreign key’s can rescue the situation…

A typical example

Let’s say you have a table called users and a user_meta table that holds multiple rows relating to the user table (use the following code in a test database).

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(30) DEFAULT NULL,
  `passwd` varchar(40) DEFAULT NULL,
  `email` varchar(120) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

CREATE TABLE `user_meta` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `meta_name` varchar(255) DEFAULT NULL,
  `meta_value` text,
  `user_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_user_meta_users` (`user_id`),
  CONSTRAINT `FK_user_meta_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

Notice the database engine in this case is InnoDB. MyISAM does not support foreign keys. Without worrying too much about the syntax, the user_meta table declares that the column ‘user_id’ is foreign and references the primary key on the user table (id).

Now that they foreign keys are referenced what can we do with them? In this case, I’ve declared ON DELETE CASCADE. This means that if we delete a user from the user table, all user_meta records for that user will be deleted automatically. If we said on UPDATE CASCADE, should the users primary key get updated, then the user_meta table will automatically update the user_id column as necessary.

There are other options too…

RESTRICT and NO ACTION. These two are similar in that nothing will get changed upon the parent table changing. SET NULL will set the foreign key table column values to null upon delete/update. SET DEFAULT can also come in handy if you have set default values to columns.

Try them out, if you havent’ used them before, I will guarantee you will find uses for them in the future.

Download sample database dump: Foreign Key Tutorial SQL Dump (87)