[arjan.tijms]

I noticed that not all my foreign key relations are actually drawn in the diagram. The symbols in my table show that the foreign keys are ‘recognized’ and the Table Info view also shows the correct relations, but no edges are being rendered in the graph. A lot of other ones are rendered, so it are just a few that seem to be missing.

Any idea?

(We’re running postgresql 7.4 on a debian linux installation. The Eclipse client runs on Windows 2000, JDK 1.4.2, Eclipse 3.01, MyEclipse 3.8.3)

[Riyad Kalla]

Hrrm, let me check with the developer of that module.

[Brian Fernandes]

hmm… this is strange.

When you say “the symbols in my table show that the foreign keys are ‘recognized'”, do you mean symbols in the Table Node in the ER-Designer ?
I’m assuming this is what you are talking about.

The only reason I can think of right now is that you have a “cross schema” relationship – ie. a relatonship between tables in different schemas. Since both tables are not in the same view – no relationship edge would be drawn.

1) Could you check if both tables contributing to your missing relationships are in the same schema ? A screenshot of the tables (if possible) would be helpful.

2) Try refreshing the diagram or creating the diagram again in case either of the tables involved in the relationship is out of date.

Thanks,
Brian.

[Brian Fernandes]

arjan,

I’m waiting for your response to this one – need to know whether this is something I should be concerned about 🙂

Did you have cross schema relationships ? If not – did you try out my suggestions mentioned above and was the issue resolved ?

Thanks,
Brian.

[gemino]

it seems i habve the same problem, so i continue where arjan.tijms stopped posting 😉

when i create my er diagram i get the following:

its really strange because both tables are connected the same way to the “buchung” table (as foreign key, marked as primary key in the buchung table)

this is my sql schema:

REATE TABLE `buchung` (
  `FK_TN_ID` int(10) unsigned NOT NULL default '0',
  `FK_VA_ID` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`FK_TN_ID`,`FK_VA_ID`),
  KEY `BUCH_VA_FK` (`FK_VA_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `teilnehmer` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `vorname` varchar(45) NOT NULL default '',
  `nachname` varchar(45) NOT NULL default '',
  `geschlecht` int(2) unsigned NOT NULL default '0',
  `adresse` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


CREATE TABLE `veranstaltung` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `kurzbezeichnung` varchar(10) NOT NULL default '',
  `titel` varchar(45) NOT NULL default '',
  `beschreibung` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

ALTER TABLE `buchung`
  ADD CONSTRAINT `BUCH_TN_FK` FOREIGN KEY (`FK_TN_ID`) REFERENCES `teilnehmer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `BUCH_VA_FK` FOREIGN KEY (`FK_VA_ID`) REFERENCES `veranstaltung` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
        

as you can see in the er diagram just the connections are not correct, the keys in the tables are shown with correct icons. also the hibernate mapping is made correctly:

        <composite-id name="id" class="BuchungKey">
            <key-many-to-one name="teilnehmer" column="FK_TN_ID" class="Teilnehmer"/>
            <key-many-to-one name="veranstaltung" column="FK_VA_ID" class="Veranstaltung"/>
        </composite-id>

thats it.

In addition to that i have a suggestion for the myEclipse help: maybe you could describe the differnet connection types with images like you already did with the ER-Designer Table Index. I think that would be helpful.

[Brian Fernandes]

gemino,

We appreciate you providing all that detail up front.
Though you say that both tables are connected in exactly the same way, if you look closer, teilnehmer is connected to FK_TN_ID and veranstaltung is connected to FK_VA_ID. It is the FK which determines the annotation for this side of the connection (not the PK).
You will notice that FK_VA_ID has not been underlined (becuase it is not unique) -whereas FK_TN_ID is unique. Hence the different annotation.

Now, usually both FK_TN_ID & FK_VA_ID would have been marked unique. But the statement
KEY `BUCH_VA_FK` (`FK_VA_ID`)
in the buchung script makes it non-unique.

I have verified this behaviour locally on our MySQL installation. We are investigating whether the UNIQUE / NON-UNIQUE interpretation of FK_VA_ID is correct.

I agree, a visual description of the annotation should have been included with the documentation. Thanks for bringing that to our notice.
FYI – the annotation on the left is “one and only one” and on the right is “one or more”

Brian.

[gemino]

thx for your really fast response.

i havent recognized that mysql created this key itself. the original create statement had contained only the primary key. i guess the thing is, that innodb wants to create a cluster key. therefore it needs ONE primary key field. if there is none, innoDB creates this key internally. in my case it looks like innodb took one of the two primary keys for the cluster key. if you add a second key (or add both fields as indices additionally to the index which is already used when used as primary key) then the ER diagram is shown correctly.
the sql statement would look like this:

CREATE TABLE `buchung` (
  `FK_TN_ID` int(10) unsigned NOT NULL default '0',
  `FK_VA_ID` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`FK_TN_ID`,`FK_VA_ID`),
  KEY `BUCH_VA_FK` (`FK_VA_ID`),
  KEY `BUCH_TA_FK` (`FK_TN_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[Author]

Posts