Designing Foreign Keys, PT II

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.

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.

Note the first preference: existing by template.

Well, what template is that?

Check your design preferences.

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.

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.

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.

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.