[Sirron Davis]

I like to use the ERD tool for documenting my database. However, the tool regularly selects the wrong symbol for relationships.

I have many one-to-many and zero-to-many relationships which show up in the drawing as one-and-only-one.

Example.
create table TM (keycol varchar(36) not null, datacol varchar(255) not null default ‘x’, primary key(keycol)) type=InnoDB;

create table TMchild (rowID int unsigned not null auto_increment, keycol varchar(36) not null, childCount int unsigned not null default 0, primary key(rowID), unique key (keycol, childCount)) type=InnoDB;

alter table TMchild add constraint fkDefn foreign key (keycol) references TM (keycol);

The above definition will show in my diagram as a one-and-only-one relationship between TM and TMChild in the ERD. Even though it is really a 0-to-many relationship.

How can I make the ERD show a proper one-(or zero)-to-many?

I am using MySQL 5.0.x and keys and foreign keys are defined in the tables.
I am using Workbench 5.5.1 GA

Has anyone found a similar issue, and is there a solution?

Thank-you

[Loyal Water]

This is the result I got when I used the tables you have posted. This is a 0-many relationship. Can you please paste a screenshot of the result you got.

Attachments:

You must be logged in to view attached files.

[Sirron Davis]

This is the ERD created from the two tables. The diagram was created with a 1-and-only-1 relationship. This is supposed to be a 0-to-many relationship. I could live with this being created as a 1-to-many also. But a 1-and-only-1 relationship is wrong.

Here is the DDL for both tables:

    create table `testDB`.`TM`(
        `keycol` varchar(36) default '' not null,
       `datacol` varchar(255) default 'x' not null,
        primary key (`keycol`)
    );

    create unique index `PRIMARY` on `testDB`.`TM`(`keycol`);

    create table `testDB`.`TMchild`(
        `keycol` varchar(36) default '' not null,
       `childCount` int unsigned default '0' not null,
        primary key (`keycol`,`childCount`)
    );

    alter table `testDB`.`TMchild`  
        add index `fkDefn`(`keycol`), 
        add constraint `fkDefn` 
        foreign key (`keycol`) 
        references `testDB`.`TM`(`keycol`);
    create unique index `PRIMARY` on `testDB`.`TMchild`(`keycol`,`childCount`);

[Sirron Davis]

I slightly modified the test files. This is the ERD with the files as originally posted.

I get a one-and-only-one relationship. This is not correct. It should be one-to-many.

Here is the DDL

    create table `testDB`.`TM`(
        `keycol` varchar(36) default '' not null,
       `datacol` varchar(255) default 'x' not null,
        primary key (`keycol`)
    );

    create unique index `PRIMARY` on `testDB`.`TM`(`keycol`);

    create table `testDB`.`TMchild`(
        `rowID` int unsigned not null auto_increment,
       `keycol` varchar(36) default '' not null,
       `childCount` int unsigned default '0' not null,
        primary key (`rowID`)
    );

    alter table `testDB`.`TMchild`  
        add index `fkDefn`(`keycol`), 
        add constraint `fkDefn` 
        foreign key (`keycol`) 
        references `testDB`.`TM`(`keycol`);
    create unique index `PRIMARY` on `testDB`.`TMchild`(`rowID`);
    create unique index `keycol` on `testDB`.`TMchild`(`keycol`,`childCount`);

[Sirron Davis]

I updated to eclipse 3.3 and MyEclipse Build id: 6.0.1-GA-200710

I still have the error. I regenerated and still get a one-to-one. This should be a one-to-many.

Build id: 6.0.1-GA-200710

[Loyal Water]

SirronD,
I have asked the dev team to look into this issue. I’ll get back to you with an update asap. Thank you for your patience.

[Sirron Davis]

I found a work-around.

I declare a specific index on the child table for the foreign key reference. This however is not ideal as I really don’t want to define unnecessary indices in my database.

Original TMchild definition

create table TMchild (rowID int unsigned not null auto_increment, keycol varchar(36) not null, childCount int unsigned not null default 0, primary key(rowID), unique key (keycol, childCount)) type=InnoDB; 

alter table TMchild add constraint fkDefn foreign key (keycol) references TM (keycol); 

Work-around TMchild definition

create table TMchild (rowID int unsigned not null auto_increment, keycol varchar(36) not null, childCount int unsigned not null default 0, primary key(rowID), unique key (keycol, childCount), key (keycol)) type=InnoDB;

alter table TMchild add constraint fkDefn foreign key (keycol) references TM (keycol);

Note: The difference is in the work-around that I define an index on keycol using the syntax “key (keycol)”. This index is not required, because of the unique index on keycol + childCount.

Generated ERD using Work-around

Generated DDL for TMchild after using work-around table definition

    create table `testDB`.`TMchild`(
        `rowID` int unsigned not null auto_increment,
       `keycol` varchar(36) default '' not null,
       `childCount` int unsigned default '0' not null,
        primary key (`rowID`)
    );

    alter table `testDB`.`TMchild`  
        add index `fkDefn`(`keycol`), 
        add constraint `fkDefn` 
        foreign key (`keycol`) 
        references `testDB`.`TM`(`keycol`);
    create unique index `PRIMARY` on `testDB`.`TMchild`(`rowID`);
    create unique index `keycol` on `testDB`.`TMchild`(`keycol`,`childCount`);
    create index `keycol_2` on `testDB`.`TMchild`(`keycol`);

Somehow the ERD generation tool is not properly identifying the relationship when the foreign key is a (high order) part of a multi-column unique index.

Many parent/child type definitions will contain only a single multi-column unique index with reference to the parent as a high-order column within that multi-column key. This parent/child relationship must be identified in the ERD as a one-to-many relationship.

And it is not practical to define a totally useless key in these child tables.

[Author]

Posts