When using the data modeler, we assume when you add a foreign key, you haven’t already created the ID column.

So we create that for you.

Here’s what that looks like.

I draw the relationship.

It's creating a new column for me to use for the relationship/FK - I don't want that.
It’s creating a new column for me to use for the relationship/FK – I don’t want that.

I don’t want that.

I want it to use USER.USER_ID instead.

Click on the Column, and toggle it to USER_ID.

THAT ONE - it will only show columns eligible for the FK constraint...data type, uniqueness, nullability.
THAT ONE – it will only show columns eligible for the FK constraint…data type, uniqueness, nullability.

When I click ‘Apply’ or ‘OK’ – the generated column goes away.

YES
YES

If you don’t like being asked, you can set the default action when deleting the FK in the preferences: ‘Delete FK columns strategy’

  • Delete generated FK columns
  • Do not delete generated FK columns
  • Ask Before Delete

Ok, let’s look at the code.

CREATE TABLE table_a (
    id        INTEGER NOT NULL,
    user_id   INTEGER NOT NULL
);
 
ALTER TABLE table_a ADD CONSTRAINT table_a_pk PRIMARY KEY ( id );
 
ALTER TABLE table_a ADD CONSTRAINT table_a_user_fk FOREIGN KEY ( user_id )
    REFERENCES "USER" ( user_id );

Tip: Draw from Parent to Child

When I go to draw a FK, I always go from child to parent…I think backwards for some reason.

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.

6 Comments

  1. Hello Mr. Jeff Smith,
    I’ve got the same question as Jim (JimatNOAA), but I don’t see it answered yet. Defining the roles of relationships is critical to the logical modeling process, not just in physical modeling … does ODM support this?
    Thanks,
    Geof

    • Well, looks like I figured it out. It has to do with the Relation Properties, Attributes portion. It was a little buggy, and I still cannot get the migrated, and now role-named attributes to show on the logical model …

  2. Thank you for this! I wanted to share a common column on the child table between three parent tables, and this showed how to do that!

  3. I am creating a model based in a DDL that already exists, where there are not FK’s defined, all the control of parent/child is made in the application (I know, totally unusual…) but that’s my job and I have to do it!
    In this definition of the relationship, sometimes, the Data Modeler doesn’t show the column (already existent) in the child table. It shows a lot of other columns but not that I need to create the relationship. What is the criteria used by Data Modeler to show the columns?

  4. In a logical model, how do you specify an existing attribute (not a generated identifying attribute) in a child table as the FK column?

    • JimatNOAA

      In addition to the question above, I’d like to know how to go the other way, i.e. set a foreign key on an existing column in the relational model like you show above and reverse it to the logical model. It generates a new FK attribute instead of using the one I designated.

Reply To Alexandre Cancel Reply