Global Constant
Steve Nay's ramblings

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.