Professional LAMP: Linux®, Apache, MySQL®, and PHP5 Web Development

4.4. InnoDB Tables

One of the advantages to using MySQL is that you're not limited to a certain storage engine for your data. What's a storage engine? MySQL, as a server, provides the user with a consistent interface and querying language, but is only loosely coupled with the way it actually stores the data on the disk. The different storage engines in MySQL provide differing ways to store and access the data, from the trusty default MyISAM, to the sizzling speed of in-memory Heap tables, and all stops in between. One of the most flexible and powerful table types you can use is InnoDB. The InnoDB storage engine, created by Innobase Oy in Helsinki, Finland, provides MySQL with a safe and efficient way to store and process data, and gives MySQL some additional functionality not present with MyISAM or most of the other table types.

4.4.1. InnoDB Advantages

Although the default MyISAM table type gives you a mature and speedy way to store simple data structures, it is lacking a handful of the features that many developers want—and InnoDB can provide. Available by default since MySQL 4.0, InnoDB tables give you the ability to use transactions, row-level locking, and best of all, foreign key constraints.

Foreign key constraints provide a way to maintain referential integrity across linked tables. In the VehicleInventory database, the New_Vehicles and Used_Vehicles tables contain a column for model_id, to specify the model, and subsequently, the make, for each vehicle. A foreign key ...