Search code examples
sqloracleentity

Oracle SQL adding additional foreign keys

I am creating a logical model on Oracle SQL, however am having a bit of trouble. When I add a relationship between two entities, I'd expect Oracle to add the PK of the parent entity to the child entity, as a foreign key.

But instead of adding the already existing attribute as a FK, Oracle keeps adding an additional attribute with the same name but with a '_1' at the end of the name (e.g. teacher_1 instead of using the already existing 'teacher' attribute)

Any ideas why this is the case?

Solution

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

    enter image description here

    You don’t want that. You want it to use USER.USER_ID instead.

    Click on the Column, and toggle it to USER_ID.

    enter image description here

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

    enter image description here

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

    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.