Any way to: Alter table to engine=InnoDB?
Posted on 2008-06-16
My MySQL 4.x database is hosted at an ISP. I have an entire database of tables with foreign keys that I just discovered are worthless because the ISP didn't create the database as InnoDB.
CREATE TABLE TEST1
test1_id INT AUTO_INCREMENT PRIMARY KEY,
CREATE TABLE TEST2
test2_id INT AUTO_INCREMENT PRIMARY KEY,
foreign key (test_id) REFERENCES TEST1 (test1_id),
INSERT INTO TEST1 (msg) VALUES ('One'), ('Two'), ('Three'), ('Four'), ('Five');
INSERT INTO TEST2 (test_id, msg) VALUES (null, '2One');
INSERT INTO TEST2 (test_id, msg) VALUES (2, '2Two');
INSERT INTO TEST2 (test_id, msg) VALUES (8, '2Eight');
All the inserts work, i.e., the foreign key constraint fails to flag the last insert as a key violation.
But if I change the table creation statements to include " engine=InnoDB" after the last ")" and before the semicolon, the tables work as expected and the last insert gets flagged as violating the foreign key.
While this is by design of MySQL......
.....is there a way, using an ALTER TABLE [table name] or similar to convert a table to InnoDB?
Obviously, I could export the whole database using something like SQL Administrator and then edit the backup file to add the required parameter, but since this is a production database, I'd like to avoid that method so I can change one or two, test to verify the functionality with the website that uses it, and move one.