Converting MySQL tables from MyISAM to InnoDB

For MySQL 4.1, to convert the MyISAM tables to InnoDB (for transaction support and more), you have to...

1. Edit the SQL so that the foreign keys are indexed
2. Make sure that any tables containing a referenced column are created before the tables which defines the foreign keys

So, the following is an example of some SQL for MyISAM tables:

create table line_items (
  id             int           not null auto_increment,
  product_id	 int	       not null,
  order_id	 int	       not null,
  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=myisam;

create table products (
  id          int           not null auto_increment,
  title       varchar(100)  not null,
  primary key (id)
)type=myisam;

create table orders (
  id         int          not null auto_increment,
  name       varchar(100) not null,
  primary key (id)
)type=myisam;

For InnoDB, this would be:

create table products (
  id          int           not null auto_increment,
  title       varchar(100)  not null,
  primary key (id)
)type=innodb;

create table orders (
  id         int          not null auto_increment,
  name       varchar(100) not null,
  primary key (id)
)type=innodb;

create table line_items (
  id             int           not null auto_increment,
  product_id	 int	       not null,
  order_id	 int	       not null,
  index (product_id),
  index (order_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;

If you attempt to create InnoDB tables with the MyISAM SQL, you get errno: 150, which indicates problems with the foreign keys.

For more details and the other requirements of InnoDB tables, see the 14.2.7.4. FOREIGN KEY Constraints page the MySQL 3.23, 4.0, 4.1 Reference Manual.