How to make FK references less of a pain

Toad Data Modeler
1

When making non-identifying FK references, and using a standard ‘ID’ name in tables, the ‘ID’ FK reference shows up as a duplicate ID. Since this is guaranteed to be an error, why not just default it to: tablename_FKname, which will amount to: tablename_ID when ID is the usual PK name? This is a common convention and would save a lot of time.

Message was edited by: Olivercomputing

Message was edited by: Olivercomputing

2

Hi,

click Settings | Options and in section Model | Physical Model navigate to tab General. There is option Relation Attribute Name. Set the value to FK_<%ParentAttributeName%>. Currently, application variable ParentTableName is not supported that’s why when you name all PK in all parent tables as ID, it will not help significantly.

What I recommend is to name ID in your tables with table prefix. You can use a quick macro that allows you to add ID attribute to all entities quickly, add it to primary key and use table name as prefix.

See: http://www.casestudio.com/help/ProductivityPack.aspx

Then, with the settings described in first paragraph you will be able to model your database really quickly.

Regards,

Vaclav

3

Ok, but I think you’re missing my point. I absolutely do not want the table prefixing every ID column. I want it - consistently - named ‘ID’ for every single table. Furthermore this is absolutely standard for one common and growing use case: Ruby on Rails (although that is not what I’m using for the model in question.)

Perhaps two enhancements to TDM would make this easier:

  1. Add an application variable ParentTableName. Seems easy to do.
  2. Make the Relation Attribute Name finer grained, associated with the type of relationship, e.g.: For Identifying FK, Non-Identifying FK (at least, perhaps others). For an Identifying FK, with an IS-A relationship, I do in fact want to retain ID as the common name. For a Non_identifying FK, _ID would be the ideal name, if ParentTableName were available as a variable. Ideal in my case, but I’m sure others would find this capability useful in other contexts.
4

I have same trouble. I have to rename every single foreign key. Could you please add <%ParentTableName%> <%ParentTableCaption%> applications variables for Model | Physical Model -> Relation Attribute Name. Those application variables are already available in other places.

Like Olivercomputing stated I cannot use other PK names, because most of the frameworks I use expects id as PK.

Thanks,

5

Hello Ozum,

Please put your feature request into the Idea Pond, it might get enough support to get implemented.

If you’d like, I can create a macro with similar functionality for you in the meantime. It would rename all FKs according to some pattern (e.g. ID_ParentTableName). Would that be of any help?

Regards,

Lukas

6

Hi Lukas,

Thank you for your reply. I found similar macro. However for FKs, there are naming exceptions to this rule. So I cannot use a macro processing all foreign keys. I will put feature request to Idea Pond.