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.
