Relationships using candidate keys

I am reverse engineering a sql server 3rd party app that uses GUIDs as candidate keys. Although they are naturally unique identifiers for rows in the table, they are not - and I cannot make them to be - primary keys (and there is strong physical reason NOT to do so since they are arbitrary random values that would not sort usefully if they were made into pks).

However they do represent “natural” key values and should be defined as foreign keys in other tables. I say “natural” because the values are generated as instance identifiers of objects created in memory and really do represent a meaningful identifier for the objects (and associated data) of concern.

I have not been able to add a non-identifying relationship between two tables where the parent table has a GUID column that is not the primary key. I did add a unique constraint on the candidate key in the parent, but TDM still does not allow me to choose the column as the parent column for a foreign key.

Is this not possible in TDM?

Thanks!

Ken

thanks,

Ken

Hi Ken,

edit relationship and choose Parent Key that will be used for the relationship:

key-rel.png

Note: alternate key must exist in parent table. You can either edit the parent table and add there new key manually (tab Keys) or edit appropriate attribute and check the Unique checkbox. Toad Data Modeler will create the alternate key for you automatically.

Regards,

Vaclav

thanks but the drop down only will show the pk of the parent table. It does not show the candidate key, which is a GUID having properties of not null and unique. So i cannot make the 1 to m relationship between the parent table candidate key and the child table pk.

Hi, can you send me sample model to modeling@quest.com, please?

I am not able to reproduce it.

The drop down must contain all keys defined in parent table. If the column is unique, there should be an alternate key defined on tab Keys in the parent table. Please make sure there are two keys in the parent.

Thank you.

I also tested this and it worked as expected - i.e. the relationship for me shows both the PK and AK (UK) from the parent.

ok, thanks Bert. It is not working for me- the candidate key simply does not appear in the drop down, only the PK - perhaps it is user error but I don’t think so. I will send my model to Vaclav.