Questions about Foreign Key and Relationships

Toad Data Modeler
1

Hi,

I have been designing a new database and ran into few issues. I would greatly appreciate if somebody can answer the following questions. I am using TDM 5.2.

Consider the following structure of tables for further discussion.

ERD.png

It results in the following SQL code. Circles point to the corresponding question.

DDL Questions.png

  1. I understand this is the name of the constraint. Can it be to set to auto generate to include the table name? For example, ParentKey. If so, how?

  2. The child table inherits the parent tables key column. I renamed the inherited column to be “ParentId” instead of just “Id”. Fine so far. But the child column also inherited the default setting from the parent column. I don’t want that b/c the child record will point to the parent record hence no need to have a default on the child column.

  3. The child column is created as NULL-able. I understand it depends on the type of relationship and whether the “parent is mandatory”. It appears the non-identifying relationship defaults to false for the “parent is mandatory” setting. Can this be changed so that by default, for the non-identifying relationships, the default for “parent is mandatory” is true? If so, how?

  4. Same as #1 above. Can this be auto generated to include the table name? If so, how?

  5. Same as #1 and #4. Can this be auto generated to, let’s say, follow this logic - “Parent table name _ Child table name”. If so, how?

2

Hi,

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

  1. The name Key 1 represents primary key name. It cannot contain parent key name, because there is no parent for primary key. You can rename the key on tab Keys manually, or you can select entity or more entities, right click any of them and choose Macros | Rename | Rename Primary Keys. Using this macro you can rename primary keys to PK_. More information can be found in help file, see section Scripting and Customization | Macros.

    01.png
    01.png571×548 13.5 KB

    02.png
    02.png987×929 74 KB

    1. In Settings | Options in section Physical Model, change settings for Attribute Properties Propagation to Only Obligatory PK Properties.
      Then try to create the relationship again.

    03.png

    1. The only method is via custom script. (The Not Null settings depends on relationship type and values of Mandatory parent/child. Default values for Mandatory parent / Mandatory for non-identifying relationships are not reflected, we will look into the matter.)

    2. Identical to item number 1.

    3. You can either set Default Value for relationship names: More information can be found in help file.

    04.png

    Note: if you change Default Values, restart Toad Data Modeler. This is required step.

    Regards,

    Vaclav

3

Thanks.

  1. This does not work if I create and attach a domain to the PK column in the Parent table. The FK column in the Child table inherits the domain along with the default of NEWID(). What is the best way to handle this issue if using Domains?

While I have your attention, can you please also answer the following question?

  1. The word “Empty” is appearing on the “Default Value Constraint Name” field on “Attribute Properties” dialog box. For the life of me I am unable to figure out which Default Value I messed up to cause this. Can you tell me how to get rid of this so that it does not appear on each and every newly added column?
4
  1. I checked the UNIQUE checkbox on the Attribute dialog box. This added a key to the table. I want the unique key name to be as follows Unique_tablename_columnname

Which “Default Value” should I update and what should it be set to?

5

Hi,

  1. It is not recommended to use default values for Domains that are used as keys that migrate to child entities. Domains are useful, but not in all cases. We planned to introduce concept of overrideable domain values, but that would result in complex/confusing user interface.

  2. Not sure. Please send me sample model or screenshots of Default values settings, let me know if you use some custom package/script and how you use domains.

  3. You can write custom macro to rename unique keys to the desired form. The script can be called at any time - and you can easily synchronize the name in case you rename the column or table. Default settings are for all keys, there is no separate option for PKs and for unique keys.

For writing scripts/macros I recommend you to use Eclipse with TDM snippets. See this blog article: www.toadworld.com/…/eclipse-snippets-for-macros-and-scripts.aspx

Good luck,

Vaclav

6

Hi,

I sent you two emails with suggestions.

Vaclav