Composite keys foreign-key relationships
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.