Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

Foreign key to >1 table multiple columns

Help
2011-08-07
2013-05-13
  • Kevin Gurney
    Kevin Gurney
    2011-08-07

    I have three tables

    CUSTOMERS (CUSTOMER_NUMBER) (Parent table, PK is CUSTOMER_NUMBER)
    EVENTS (EVENT_CODE, CUSTOMER_NUMBER) (Parent table, PK is EVENT_CODE, CUSTOMER_NUMBER)

    CLAIMS (CLAIM_CODE, CUSTOMER_NUMBER, EVENT_CODE) (Child table, PK is CLAIM_CODE)

    The CLAIMS table needs a mandatory relation (FK) to CUSTOMERS on CUSTOMER_NUMBER
    The CLAIMS table has an optional relation (FK) to EVENTS on EVENT_CODE, CUSTOMER_NUMBER

    When I create the relations, the CLAIMS table ends up with two columns named CUSTOMER_NUMBER and I cannot seem to delete one of the columns or change the relation to realise that only one of the CUSTOMER_NUMBER columns should refer to both tables.

    If I ignore the error, export the DDL and manually modify, then I can create the relationships.

    Any help appreciated on how to force the relations to refer to the same (single) column in the child table.

    BTW. Great work on this plug-in, very impressed.

     
  • Kevin Gurney
    Kevin Gurney
    2011-08-07

    My mistake, I should have RTFM :)

    Relation by Existing Columns seems to have taken care of it.