Toad World® Forums

Foreign Key Modeling


#1

Hi everybody.

I’ve attached an image explaining the situation that I’m not able to model properly …

As shown in the picture, the TYPE field is common to all the 3 tables, and is part of the primary key of all tables too.
When I try to build the 2 foreign key, dragging from the child table (eg. TABLE_B) to the parent able (eg. TABLE_A), the fields of the child table primary key are inserted into the parent table …
This will duplicate TYPE field in TABLE_A …
I’d like to use TYPE field of TABLE_A as part of the foreign key to TABLE_B (and TABLE_C) ,
how should I pocedere to achive this goal ?

Many thanks in advance,
Andrea

IMG.gif


#2

You need to re-map the parent keys onto the existing primary key in the child relationship. Double click on your relationships and select the “foreign keys” tab. Highlight the child attribute in the right column of the dialog and press the F2 key. That will provide a popup menu where you can select the table attributes that you want to select. For you TABLE_B table, when you double-click relationship 2, change the child attribute for ID_B to ID_A. That will map the parent field to the existing field in table a and remove the duplicate ID_B attribute. See attachment.

Hi Vladka. I was never here.


#3

With attahcment.
remap_fk.doc (123 KB)


#4

Bruce, thanks so much! :slight_smile: :slight_smile:

Just a tip for Andrea - flash movie on FK mapping is available at:
http://modeling.inside.quest.com/entry.jspa?externalID=1668&categoryID=158

Have all a nice day.

Vladka


#5

Hi
Many many thanks to all.

I’ve followed your suggestions and now it works fine.
The field I need to re-map is TYPE field and not ID_B and ID_C but anyway I understodd the method suggested :wink:

Have a nice day !
Andrea
IMG2.gif


#6

Since I was never here, it should be okay for me to make an additional comment. While what you are doing sort of makes sense from a modeling perspective, I’m not sure it makes sense from in an implementation sense.

You have TABLE_A that is a child table of TABLE_B and TABLE_C. With that foreign key on the TYPE attribute of TABLE_A, when you insert into TABLE_A, the referential integrity is going to require that the TYPE has a matching parent value in TABLE_B AND TABLE_C, not TABLE_B OR TABLE_C. The value will have to be in both parent tables to satisfy the referential integrity. If that is your intention, then you are correct. If you intend that it may be in either table, you are incorrect.

This has nothing to do with TDM. It’s a data modeling thing that I discovered the hard way. Been there, done that, redesigned my database.


#7

Bruce,
that is what I wanted to model :wink:

The foreign key from TABLE_A to TABLE_B is based on 2 fields, TYPE and ID_B, and not only on the field TYPE :wink: (and is the same thing with TABLE_C) …
When I insert a record in TABLE_A, the referential integrity requires that the couple TYPE, ID_B of TABLE_A need to be in TABLE_B in the field …

Thanks,
Andrea