For MySQL 4.1, to convert the MyISAM tables to InnoDB (for transaction support and more), you have to...
- Edit the SQL so that the foreign keys are indexed
- 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.