Page 1 of 1

Switching from MyISAM to InnoDB

Posted: Thu Mar 11, 2010 8:52 am
by alex.barylski
I have 20 or so tables used by a maintenance management system. Aerospace has so many caveats and exceptions to the rule of thumb, most of these tables are to accomodate these weird exceptions. Lots of many to many relationships.

It's very easy to lose yourself in the existing table schema, without first mapping out the relationships between various tables.

SqlYog has a schema diagram tool which will build a visual reference for you, BUT, it seems to assume InnoDB is used.

So I am starting to think:

1. Transactional support would be a bonus and make twice daily backups less critical
2. Foriegn key associations will let diagramming tools to build the map for me saving me tonnes of time (not to mention new developers)

What I would like to know, is whether forign key constrainst in InnoDB will have cascading deletes? When I remove a master record will all of it's associated tables/records be deleted as well? Will switching to InnoDB cause any issues, other than slightly less performance.

Cheers,
Alex

Re: Switching from MyISAM to InnoDB

Posted: Thu Mar 11, 2010 10:48 am
by AbraCadaver
You can set the foreign key constraint to RESTRICT, SET NULL or CASCADE for updates and deletes.

By the way, have you tried MySQL workbench? You can import your existing DB and it generates a schema. Might be worth a look.

Re: Switching from MyISAM to InnoDB

Posted: Thu Mar 11, 2010 2:07 pm
by alex.barylski
Lots of MySQL tools will generate schema diagrams, although I am assuming even MySQL monitor requires you to have used InnoDB tables and not MyISAM???

Cheers,
Alex

Re: Switching from MyISAM to InnoDB

Posted: Thu Mar 11, 2010 2:23 pm
by AbraCadaver
PCSpectra wrote:Lots of MySQL tools will generate schema diagrams, although I am assuming even MySQL monitor requires you to have used InnoDB tables and not MyISAM???

Cheers,
Alex
MySQL Workbench will import myisam, obviously without the showing relations. But you can then use it to convert the tables and add your foreign keys graphically. Then you'll have your new diagram, with relations and a new database with innodb and foreign keys.