Data Modeler: Foreign Key Create New Column vs Use Existing

thatjeffsmith SQL Developer 2 Comments

Tell Others About This Story:

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.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 2

  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?

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

Leave a Reply

Your email address will not be published. Required fields are marked *