Foreign keys in MySQL: InnoDB and MyISAM

To create a foreign key in a MySQL database, there are a few things to keep in mind:

• Any tables using the foreign keys must use the InnoDB engine (MyISAM, the default database engine, doesn’t support them)
• The foreign key field must be indexed

Jim Epler posted a great tutorial (including screencasts) explaining the process of adding foreign keys.

As far as the differences between InnoDB and MyISAM go, here are my findings:

• MyISAM is based on a proven, reliable code base
• MyISAM is very fast and efficient for normal operations, such as selecting and inserting
• If you need relational design (esp. foreign keys), you’ll need InnoDB
• InnoDB also supports transactions and row-level locking (as opposed to the table locking of MyISAM
• InnoDB has better crash recovery, especially on large data sets

See Mike Bernat’s post and this page on INetU. The MySQL developer documentation also provieds a detailed comparison of all the supported database engines.