MySQL Bugs: #49999: UI allows adding foreign keys to tables with engines that do not support them

Bug #49999	UI allows adding foreign keys to tables with engines that do not support them
Submitted:	30 Dec 2009 16:05	Modified:	5 Feb 2010 10:36
Reporter:	Walter Stanish	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.2.11 OSS Beta 4842	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	engine, key, UI

Description:
Using MySQL workbench, it is possible to create a relationship between tables that do not support them (eg: MyISAM).

How to repeat:
 - Start MySQL Workbench
 - Create two InnoDB tables
 - Use 1:n tool to draw a relationship
 - Relationship is created... no warning or anything

Suggested fix:
When creating a relationship, the program should detect whether the engine for the tables in question actually supports it.  If not, it should pop up a dialog and prompt for a course of action.  For example:

 Table engine 'MyISAM' on table 'sometable' does not support foreign keys.

 If you wish to change the table engine, please specify the new engine:
   [InnoDB (v)]     *help

 [OK] [Retry] [Cancel]

Having a [Retry] option would allow the user to go back to the model and manually resolve the situation before making a decision.

Having a new engine default would save time for the user.  [InnoDB (v)] is supposed to represent a dropdown ... being the subset of all available engines that actually support foreign keys.

*help could be a link to a relevant portion of the MySQL website explaining critical engine differences or the notion of foreign keys.

I think storage engine matters only when you are going to implement your design, that is, to generate SQL script or syncronyze your ERR Diagram with the database. At this moment validation should happen and warnings should be generated. Do you agree?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I think that the purpose of a modelling tool is to produce a valid model.

Therefore logical errors should be brought to the user's attention ASAP.