Add FK - cannot remove any of the keys after TDM inserts

Toad Data Modeler
1

I rev engineered a MS SQL 2005 schema. When I try to map a NEW FK between 2 tables, the relationship works if there is only one key to map, however if there is more than one key in the primary table they are all placed in the child table. I only need to map one of these keys as an FK to the child the other I do not know how to remove.

I have tried the FK dialog but it only lets me change the column not remove one of the two shown. How is this done?

BEFORE:

Parent Child

ID PK ID PK
PROJID PFK TABLEID
col3 col3
col4 col4
col5

Add identifying relationship from parent to child.
AFTER:

Parent Child

ID PK ID PK
PROJID PFK TABLEID
col3 col3
col4 col4
col5 ID PFK
PROJID PFK

TDM adds ID and PROJID to the child. I am trying to make tableid an FK to the ID of the parent. I can do that but cannot remove PROJID from the child - HOW? Neither the fk dialog nor the entity dialog allow to delete the projid.

2

Hello,

Please find attached a .doc file with description how to solve the problem. - I hope I understand well what you require.

If I’ve misunderstood, please write me back.

Concerning FKs in TDM, their removing, deletion etc., some helpful information can be found also in the following threads:
http://modeling.inside.quest.com/thread.jspa?threadID=3012&tstart=45
http://modeling.inside.quest.com/thread.jspa?threadID=3085&tstart=15

Regards,

Vladka
fk.doc (43.5 KB)

3

I have tried all combinations and it still fails. NOTE however that the dialog (in your doc) for edit relationship is entirely different then my trial. My trial versions are 2.25.0.1 and template ver 2.29. I also note the year is 2006. Is the trial download not updated to a current release?

I will check with my contact to verify to see if I can’t expedite this, but I still can’t delete the FK no matter waht type of relationship. Also, using non-identifying makes the problem worse because now the dialog for FK does not even have tableid in the dropdown list as a key to change too.

4

Hi,

What a funny misunderstanding… In this Modeling community, only issues on Toad Data Modeler 3 (BETA at the moment) are discussed.

So, the solution I sent you is for this product - Toad Data Modeler 3.

Questions on TDM 2 should be directed to support@quest.com or to TDM2 Yahoo group at:
http://tech.groups.yahoo.com/group/toaddm/

However, now you can test the RE of your database in our BETA.

Anyway, I’ll check TDM2 2.25 and get back to you in a while with some advice on your trouble.

Please stay in touch.

Vladka

P.S. Just a quick note: You cannot delete FK itself. The FK is transferred by relationship, so you would have to delete relationship to delete a FK.

Message was edited by: vladka

5

Hi,

I’m back with screenshots of TDM 2 - 2.25 - see the attached .doc.

The steps are the same, however in TDM 2, the FK mapping is different.

Concerning the Relationship 2 - it doesn’t matter what type it is. The TABLEID attribute will be offered for the FK mapping in any case.

If you have any questions, please write me back.

Thanks.

Vladka
fk_tdm2.doc (52 KB)

6

Thanks for your efforts. I sent a note to my support contact at Quest to get TDM3. I have tried all combinations. The example I gave you is simple. i have many more tables and FK’s that are mapped. For example, assume two FK’s to one table from 2 other parent tables. The parents have a total of 5 keys(Pk’s and FK’s). If I use identifying to the child I end up with those 5 keys in the child plus the real Fk which is another column altogether. If I use non-identifying then my target column doesn’t even show up in the drop down for FK’s.

I’m pleased that you responded quickly and tried to help but there are bugs in this version and this does not work. I am fighting the tool when I have used much more expensive tools in the past that have no problem with the relationships I’m identifying. So I’m trying to work with this tool but I’ll need the new version so I’ll wait to hear from my rep.

I’m working around it by just renaming the offending keys as N/A for now. The table looks like crap but it’s correct if one ignores the N/A columns. I don’t need to rewrite this model back to the DB so it’s okay for now.

Thanks.

7

Hi,

Important information for you.

1. TDM3 issue

I sent a note to my support contact at Quest to get TDM3.

As I wrote previously, BETA version of TDM3 is only available. This modeling community is a place where you can discuss TDM3 issues and join the TDM3 BETA testing program.
Please read information on the BETA program at:
http://modeling.inside.quest.com/beta.jspa

Also, let me quote this:

Toad Data Modeler 3 BETA is supported ONLY via this community directly by our Development team. Quest Software Support team does NOT provide any support to the BETA. All your questions and requirements should be sent to: modeling@quest.com or published in our Discussion Forum.

You can download BETA from:
http://modeling.inside.quest.com/ann.jspa?annID=95

2. To your trouble with TDM2 2.25 - foreign keys

...there are bugs in this version and this does not work.

I'm not sure what bugs you mean. Let me explain how TDM works:

As soon as you create an identifying relationship, Toad Data Modeler automatically adds a copy of the primary key of the Parent table to the Child table - Primary Foreign Key (PFK). (The foreign key in Child table is a part of the primary key.)

As soon as you create a non-identifying relationship, Toad Data Modeler automatically adds a copy of the primary key of the Parent table to the Child table - Foreign key (FK). (This foreign key in Child table is not a part of the primary key.)

In other words, foreign keys (PFKs and FKs) are imported from parent entities to child entities automatically while creating relationship. Foreign keys cannot exist without relationships. Therefore, you are not allowed to delete FK or PFK from Child tables either. To delete them, you have to delete appropriate relationship.

* What can happen after RE...
Example situation:
You have reverse engineered a model without relationships. In TDM, a new model has been created. However, information about foreign keys has been lost as in TDM stands that foreign keys cannot exist without relationships.

You have created a new relationship and so new foreign key has been added to the child entity automatically. Now the child entity has two attributes of the same name (the original attribute and the newly added foreign key).

Solution: Foreign Keys Mapping

Sources of Info:

Well, I hope this information will clarify this problem a bit.
Anyway, I suggest to discuss the problem with TDM 2 outside this community. Possibly, the problem is anywhere else. Please contact me via modeling@quest.com with your questions and detailed description of your problem. You can also send us your model. Our TDM team will check it out.

Thanks.

Regards,

Vladka

Message was edited by: vladka