0 Replies Latest reply on Aug 19, 2003 11:28 AM by aehlers

    Composite keys foreign-key relationships

    aehlers Newbie

      I've got two tables, which have a one-to-one relationship with each other. (yes, I know I could combine them, but please bare with me :) Unfortunately, this is the way set out to me (as the developer).

      Tables are as follows:
      RACE:
      PK: RAC_ID

      FK: ACC_ID (FK of ACC_RACE.ACC_ID)


      ACC_RACE:
      PK (composite): RAC_ID (FK of RACE.RAC_ID)
      ACC_ID


      The RACEPK entity only have RAC_ID within it.

      ACC_RACEPK has both RAC_ID and ACC_ID defined to form the primary key.

      In the jbosscmp-jdbc.xml I've defined the relationship as follows:

      <ejb-relation>
      <ejb-relation-name>ACC_RACE-RACE</ejb-relation-name>
      <foreign-key-mapping/>
      <ejb-relationship-role>
      <ejb-relationship-role-name>
      ACC_RACE
      </ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>aCC_ID</field-name>
      <column-name>ACC_ID</column-name>
      </key-field>
      <key-field>
      <field-name>rAC_ID</field-name>
      <column-name>RAC_ID</column-name>
      </key-field>
      </key-fields>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>
      RACE
      </ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>rAC_ID</field-name>
      <column-name>RAC_ID</column-name>
      </key-field>
      </key-fields>
      </ejb-relationship-role>
      </ejb-relation>


      My problem is as follows:
      I retrieve a race by the following query:
      <![CDATA[SELECT OBJECT(r) FROM RACE r WHERE r.aCC_RACE.aCC_ID = ?1 AND r.mEET.mEET_ID = ?2]]>

      In this query I actually "refer" to the aCC_RACE relationship - and it works fine. The correct race is returned.

      But, when call race.getACC_RACE() (which should return the Local Interface of ACC_RACE to which the RACE is associated), it returns NULL.

      I think that JBoss mixes up its fields with the primary key, i.e. when it does a search for the ACC_RACE, it searches for RAC_ID = {ACC_ID} and ACC_ID = {RAC_ID} instead of RAC_ID = {RAC_ID} and ACC_ID = {ACC_ID}.

      The reason I know this is because, for example, if I look for race=1, acc=2 (and there is actually a record with acc=1,race=2), that one would be returned.

      Is there ANY way I can "help" JBoss know which field to map where. I've read a few places that you can't have a foreign-key as part of a composite primary key - is that true?

      Any help would be greately appreciated, because this is really doing my head in.

      Many Thanks
      A.