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
Switching from MyISAM to InnoDB
Moderator: General Moderators
-
alex.barylski
- DevNet Evangelist
- Posts: 6267
- Joined: Tue Dec 21, 2004 5:00 pm
- Location: Winnipeg
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: Switching from MyISAM to InnoDB
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.
By the way, have you tried MySQL workbench? You can import your existing DB and it generates a schema. Might be worth a look.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
-
alex.barylski
- DevNet Evangelist
- Posts: 6267
- Joined: Tue Dec 21, 2004 5:00 pm
- Location: Winnipeg
Re: Switching from MyISAM to InnoDB
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
Cheers,
Alex
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: Switching from MyISAM to InnoDB
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.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_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.