I'm currently learning Ruby on Rails by going through the book Agile Web Development with Rails by Dave Thomas and David Heinemeier Hansson and wanted to ensure referential integrity by using MySQL's InnoDB table type. If you create the tables exactly as listed in the book (on page 502 and elsewhere), the database will be created without errors, but also without true use of the foreign keys for referential integrity (unless your default table type is InnoDB), because MyISAM pretty much ignores the foreign key declarations. However, if you're using a MySQL version prior to 4.1.2, simply changing your table type to InnoDB will not work either, and you'll get ERROR 1005: Can't create table './depot_development/line_items.frm' (errno: 150) error messages. This is because, according to MySQL's documentation on foreign keys, you need to declare the foreign keys as indexes. Here's what the sql code to create the line_items table looks like after adding the indexes:
create table line_items (
id int not null auto_increment,
product_id int not null,
order_id int not null,
quantity int not null default 0,
unit_price decimal(10,2) not null,
index (order_id),
index (product_id),
constraint fk_items_product foreign key (product_id) references products(id),
constraint fk_items_order foreign key (order_id) references orders(id),
primary key (id)
)Type=InnoDB;
With newer MySQL versions, the indexes are created automatically and you shouldn't see this error.