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.
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.
When I click ‘Apply’ or ‘OK’ – the generated column goes away.
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.
Comments 2
In a logical model, how do you specify an existing attribute (not a generated identifying attribute) in a child table as the FK column?
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.