Problem editing Foreign Keys

Toad Data Modeler
1

I have a model I reverse engineered from a Microsoft Access 2010 database. However, several relationships did not import or get set up properly. When I manually create a relationship between two tables, I am unable to edit the foreign keys. Basically, nothing appears in the Foreign Keys list box and I am unable to manually add a new relationship. I am using the latest version 4.2.6. I am guessing it is locking the relationships because the table does no have primary keys, which is not my concern as I really just need to represent the relationship that should be there.

On the flipside, the table I am joining to has 3 primary keys. When I try to set up a non-identifying relationsihp, it forces all three keys into the target table. I understand why it does this, but I only needed (wanted) two of the keys to map to the target table, and cannot remove the third relationship in the Foreign Keys tab.

Understand, I did not design the db, I mainly wanted to establish the relationships between the tables for documentation purposes because the orignal designer did not handle this. Is there any flexibility with editing Foreign Keys or can this be a feature request to “override” the current locked-in business rules of TDM?

Thank you for your help,
Tom

2

Hi,

thanks for your questions. I will try to answer them one by one:

  1. You should not be able to edit foreign keys in child table. In TDM primary key columns migrate to child tables automatically. In case you wish to change e.g. data type, edit the primary key in parent table and the change will be propagated to child table. If there is no primary key then there cannot be foreign key column in child table (unless you create alternate key and use the alternate key for relationship).

  2. There always can be only one primary key in parent table. You can have single primary key that contain three columns, but when you draw relationship from parent to child table, all the three columns will migrate. If you wish to use all the three columns in parent table as a key, don’t change anything, keep the primary key defined as is (I suppose there really is one key with three columns). In addition, on tab Keys, create another key and put there the two columns you wish to have in child table. Then draw new relationship from parent to child. Edit the relationship and on tab Foreign Keys select the new key from the Parent Key combo box. This will do what you expect. (Attributes cannot be manually removed from foreign keys, this would result in incorrect model.)

  3. You can draw lines, but I recommend to make the model as clear as correct as possible :slight_smile:

Regards,

Vaclav