Bug #62239 Automatically detect when a relationship is one-to-many or one-to-one
Submitted: 23 Aug 2011 13:27 Modified: 2 Nov 2011 16:26
Reporter: Craig Fowler Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Workbench: Modeling Severity:S4 (Feature request)
Version:5.2.23, 5.2.35 OS:Any
Assigned to: CPU Architecture:Any
Tags: relationship

[23 Aug 2011 13:27] Craig Fowler
Description:
When creating/working with relationships in WB's modelling interface, whether or not a relationship is "identifying" is automatically detected based on whether the key columns are part of the PRIMARY KEY or not, however whether or not the relationship is 1-n or 1-1 is not detected automatically and must be configured manually.

The various relationship-creation tools go some way to handling this (creating the appropriate type of relationship) but if a relationship is to be altered then it must be edited manually by double-clicking the relationship line and editing it's foreign key setting.

Actually editing this setting (toggling between 1-to-many and 1-to-1) seems to have no effect except updating the rendered line in the diagram.  I would think that "the act of making a relationship one-to-one" in a diagram would also take whatever measures are required to enforce such a relationship in the schema (that is, the creation or removal of a UNIQUE INDEX on the foreign key column).  Also the creation of indexes that would result in forcing a relationship to become 1-to-1 could also immediately change the rendered state of a relationship line accordingly.

Would it be useful to link this functionality with the creation of a UNIQUE INDEX (or removal of that index) on the foreign key column(s)?  This index-creation would actually enforce that 1-to-many or 1-to-1 relationship status within the schema.

Equally, in a similar way to how a relationship "instantly becomes" an identifying relationship if it spots that its foreign key columns are added to that table's primary key, a relationship could "instantly become" 1-to-1 if a unique index is added covering the relationship's foreign key columns (and vice-versa becoming 1-to-many if such an index is removed).

How to repeat:
N/A - feature request
[2 Nov 2011 16:26] Valeriy Kravchuk
Thank you for the feature request.