0

I am trying to add an optional foreign key in Oracle SQL Developer Data Modeler v4.1.0.873(Build 873), as per diagram below:

enter image description here

The issue is that I can only make such a relationship if it is mandatory (note solid black line - I need it to be dotted). The 'Mandatory' checkbox cannot be unchecked, or where it can be, I get:

MKT_DATA_BONDS__FK: FK cannot be optional because some columns are part of the PK

How do I show this relationship in the model? (We don't want the BONDS table to contain all bonds). I tried adding an index (on BOND_ID) but wasn't able to reference that either.

3
  • What do you mean by "We don't want the BONDS table to contain all bonds)." ? What should be stored in "BONDS" then ?
    – a1ex07
    May 15, 2015 at 16:03
  • Just those bonds that the company holds, or has an interest in. The point is that there may be records in the MKT_DATA_BONDS table that don't have a corresponding record in the BONDS table. There is probably a good argument to exclude those records from MKT_DATA_BONDS (given the company is not interested in them) but I'm still interested to know if the constraint can be made optional..
    – Allan Bowe
    May 15, 2015 at 16:23
  • 1
    Then you need to redesign table structure. If MKT_DATA_BONDS can have records which are not in BONDS then it implies BONDS is details table and needs FK to MKT_DATA_BONDS . However, FK in Oracle must refer to unique constraint in the main table (not to part of constraint)
    – a1ex07
    May 15, 2015 at 17:39

1 Answer 1

0

You have the foreign key in the wrong table. Your defining entity is in the MKT_DATA_BONDS table. The refering entity is in the BONDS table. Each BOND must be defined in MKT_DATA_BONDS. The "optional" part is carried out by the fact that not all market bonds need be represented as a company bond.

To put it in OO language, market bond is the super or parent class. A company bond is one (of possibly several) sub or derived classes. Subclasses know about super classes, but the super class doesn't know about (or refer to) any subclass.

1
  • I think the main point here is that a 'defining entity' is mandatory in the oracle data model, and that any FKs must reference a unique constraint in that regard (as raised by @a1ex07). For our purposes (given that bonds are not unique in our MKT_DATA_BONDS table) we may go with a separate bonds table (with all bonds), or we may just override those constraints in the physical implementation. Thanks for the advice!
    – Allan Bowe
    May 18, 2015 at 10:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.