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 Senior Principal Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

2 Comments

  1. 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.

Write A Comment