Bug #74178 WB, modeling, Reverse engineering, cardinalities accuracy
Submitted: 1 Oct 2014 14:37 Modified: 9 Feb 2018 15:58
Reporter: Fran├žois de Sainte Marie Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Workbench: Modeling Severity:S4 (Feature request)
Version:6.2.3 OS:Microsoft Windows (Microsoft Windows 8.1 Pro)
Assigned to: CPU Architecture:Any
Tags: Cardinalities, WBBugReporter

[1 Oct 2014 14:37] Fran├žois de Sainte Marie
Description:
MySQL Workbench 6.2.3
Modeling : New Model > Import > Reverse engineering MySQL Create Script. 
Source script : 
CREATE TABLE T1 (A1 INT NOT NULL, B VARCHAR(8) NOT NULL, CONSTRAINT T1_PK PRIMARY KEY (A1)) ;
CREATE TABLE T2 (A1 INT NOT NULL, C VARCHAR(8) NOT NULL, CONSTRAINT T2_PK PRIMARY KEY (A1)) ;
ALTER TABLE T2 ADD CONSTRAINT T2_T1_FK FOREIGN KEY (A1) REFERENCES T1 (A1) ON DELETE CASCADE ON UPDATE NO ACTION ;
After Reverse, cardinality of the relationship between T1 and T2 is set to 1..N (surjection) instead of 0..1 (injection). In fact, in data modeling, T2 should be understood as a specialization of T1 (a subclass in ULM or E/R). After reverse, I have to replace many, many surjections by injections, i.e. for each table where a foreign key, is also a primary key (or an alternate key). 

How to repeat:
New Model > Import > Reverse engineering MySQL Create Script

Source :

CREATE TABLE T1 (A1 INT NOT NULL, B VARCHAR(8) NOT NULL, CONSTRAINT T1_PK PRIMARY KEY (A1)) ;
CREATE TABLE T2 (A1 INT NOT NULL, C VARCHAR(8) NOT NULL, CONSTRAINT T2_PK PRIMARY KEY (A1)) ;
ALTER TABLE T2 ADD CONSTRAINT T2_T1_FK FOREIGN KEY (A1) REFERENCES T1 (A1) ON DELETE CASCADE ON UPDATE NO ACTION ;

Suggested fix:
To solve this modeling error : for a given child table T2, if a FK (referencing a mother table T1) is also the PK of T2 (or one of it's alternate keys), then set the cardinality to 0..1 when linking T2 and T1.
[9 Feb 2018 15:58] MySQL Verification Team
Thank you for the feature request.