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.