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.
I don’t want that.
I want it to use USER.USER_ID instead.
Click on the Column, and toggle it to USER_ID.
When I click ‘Apply’ or ‘OK’ – the generated column goes away.
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.
6 Comments
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 …
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!
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?
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.