I say designing, because I’m going to be talking about how to do this in SQL Developer Data Modeler.

And I say Part Two, because in a previous post I showed how to draw a foreign key in your relational design.

Animated GIF showing how to draw a foreign key in Oracle SQL Developer Data Modeler.
Animated GIF showing how to draw a foreign key in Oracle SQL Developer Data Modeler.

This post discusses how to control the generation of the foreign key column:

  • to generate or not
  • if generated, how to name it

Let’s start by defining the ‘foreign key column.’ If we have an employees and departments table, with each employee assigned to a department – the employees table column storing the department ID which REFERENCES the primary key column in the departments table – that is the ‘foreign key column.’

When you draw your foreign key relationship in the diagram, the modeler can attempt to use an existing column for the foreign key column, or it can generate a new one.

This is controlled by the preferences.

On New Foreign Key...
On New Foreign Key…

Note the first preference: existing by template.

Well, what template is that?

Check your design preferences.

If you have a column name which follows the naming template definition...
If you have a column name which follows the naming template definition…

So if there’s a qualified and existing column available, the modeler will choose that over creating a new one for you. The data types will have to be simpatico.

So on my simple scenario, I have tables table1 and TABLE_2. I created a PK col for the parent, and PK column for the child table. In the child table I also manually created a column that would be used to store the lookup value to the parent table.

And since I somehow managed to follow the template rule for the foreign key column name, when I drew the foreign key, it was able to use this column instead of creating a new one.

Please don't judge me by these horrible examples and complete lack of naming consistency.
Please don’t judge me by these horrible examples and complete lack of naming consistency.

In defense of my horrible example, I did write these up at 5AM this morning…

When creating the foreign key, the properties dialog does allow you to configure the columns involved.

Notes
Notes

If the modeler creates a new column for you, and you choose to use an existing, it will prompt you to delete the generated column for you.

This is controlled as well – if you don’t want to be prompted to delete the generated column, check the modeler preferences under, Model – Relational – Delete FK Column strategy.

Can't imagine why you'd want to leave the generated column in place if you're not going to use it.
Can’t imagine why you’d want to leave the generated column in place if you’re not going to use it.

Ok, I Want the New Column, but I Hate the Name You Give It

Go back to the templates – they control everything.

This is defined by DESIGN. So not in the data modeler preferences, but right click on your design, and open the design properties.

Don’t like {ref table}_{ref column} or {child}_{parent}_FK? You can change those to just about whatever you’d like.

There are a list of pre-defined variables that will allow you to incorporate the names from other objects to build your dynamically named constraint and column names.

You can of course always override these manually in your design's object property dialogs.
You can of course always override these manually in your design’s object property dialogs.
thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

4 Comments

  1. I’m new to SDDM and cannot seem to find the design preferences screen/dialog. How do you access the “design preferences”? (Using 4.1.3).

  2. Sorry no defense for bad naming 🙂 Don’t write so early.

    BTW – you also missed tagging the ID column in Table_2 as the PK column (which you mentioned in the post)

Reply To Kent Graziano Cancel Reply