A humble suggestion for the development team....
I went through and looked at some of the postings regarding using ALTER TABLE with InnoDB tables and the resulting loss of foreign key constrains. I was disappointed to learn about the need to use the create/drop workaround. The main reason I considered using InnoDB tables was to have a MySQL database that could ensure referential integrity. The thought that a command (ALTER TABLE) would silently break RI makes me very uneasy, and lessens the value I see in using MySQL. (OK, MySQL-Max) I understand that in MySQL the alter table command may in fact transparently do a create, drop and/or rename of tables, regardless of table type. But it still makes no sense to me that the ALTER TABLE command does not maintain foreign key constraints for the InnoDB table type. If MySQL does an internal create and drop to execute an alter table statement, and the table in question has a primary key, isn't the primary key transparently recreated? Isn't the same true for all other constraints?? (If not, please let me know!!) Why then shouldn't MySQL recreate the foreign key constraints too? If the reason is because the file is temporarily copied to a non InnoDB table type, couldn't the foreign key information be held temporarily in another object of some sort and applied after conversion back to InnoDB? Of course, I understand that there are priorities in development, and one should not expect to have all the bells an whistles the right away. That said, I shudder every time I open myPHPAdmin and see how easy it would be for any developer or administrator to break foreign key constraints. Therefore, I was hoping that you could consider adding foreign key support to the ALTER TABLE command, or at least consider adding an option such as "innodb_fail_alter_table_with_fk" that would warn or fail the alter table statement if the table type was InnoDB and foreign keys were going to be dropped. If this latter option was implemented, it would prevent RI from being quietly lost. Thanks much, -Bob --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
