problems storing complex objects to the database

Hello,
We are currently using OJB 1.0.1 . We have a table that is a child to two different tables. These two parent tables have composite primary keys and they share part of the keys in the child table.  The child has a 1:1 relation to both the parents. 

The tables look something like this:

CREATE TABLE "CHILD"  (
    "CHILD_ID" INTEGER NOT NULL PRIMARY KEY, 
    "PARENT_ID" INTEGER , 
    "ABC" INTEGER , 
    "PARENT2_ID" INTEGER )   
ALTER TABLE "CHILD" 
 ADD CONSTRAINT "PARENT2_FK" FOREIGN KEY
  ("PARENT2_ID",
   "ABC")
 REFERENCES "PARENT2"
  ("PARENT_ID",
   "ABC")
ALTER TABLE "CHILD" 
 ADD CONSTRAINT "PARENT1_FK" FOREIGN KEY
  ("PARENT_ID",
   "ABC")
 REFERENCES "PARENT"
  ("PARENT_ID",
   "ABC")
 
 CREATE TABLE "PARENT"  (
    "PARENT_ID" INTEGER NOT NULL, 
    "ABC" INTEGER NOT NULL)   
ALTER TABLE "PARENT" 
 ADD CONSTRAINT "PK1" PRIMARY KEY
  ("PARENT_ID",
   "ABC");

   
 CREATE TABLE "PARENT2"  (
    "PARENT2_ID" INTEGER NOT NULL, 
    "ABC" INTEGER NOT NULL)   
ALTER TABLE "PARENT2" 
 ADD CONSTRAINT "PK1" PRIMARY KEY
  ("PARENT2_ID",
   "ABC");

The OJB Classes::
 class Child{
   private Integer    childId;
   private Integer    parentId;
   private Integer    abc;
    private Integer    parent2Id;
    //rel
   private Parent parent;
   private Parent2 parent2;
  }

 class Parent{
   private Integer    parentId;
   private Integer    abc;
   //rel
   private Child child;
  }

 class Parent2{
   private Integer    parent2Id;
   private Integer    abc;
   //rel
   private Child child;
  }

The repository mapping:

 <class-descriptor class="Child"
 table="CHILD">
  <field-descriptor name="childId" column="CHILD_ID" primarykey="true" jdbc-type="INTEGER"/>
  <field-descriptor name="parentId" column="PARENT_ID" jdbc-type="INTEGER"/>
  <field-descriptor name="abc" column="ABC" jdbc-type="INTEGER"/>
  <field-descriptor name="parent2Id" column="PARENT2_ID" jdbc-type="INTEGER"/>
  <reference-descriptor name="parent" class-ref="Parent">
   <foreignkey field-ref="parentId" />
   <foreignkey field-ref="abc" />
  </reference-descriptor>
  <reference-descriptor name="parent2" class-ref="Parent2">
   <foreignkey field-ref="parent2Id" />
   <foreignkey field-ref="abc" />
  </reference-descriptor>
</class-descriptor>
 
 <class-descriptor class="Parent" table="PARENT">
  <field-descriptor name="parentId" column="PARENT_ID" primarykey="true" jdbc-type="INTEGER"/>
  <field-descriptor name="abc" column="ABC" primarykey="true" jdbc-type="INTEGER"/>
  <reference-descriptor name="child" class-ref="Child">
   <foreignkey field-ref="parentId" />
   <foreignkey field-ref="abc" />
  </reference-descriptor>
</class-descriptor>

 <class-descriptor class="parent2" table="PARENT">
  <field-descriptor name="parent2Id" column="PARENT2_ID" primarykey="true" jdbc-type="INTEGER"/>
  <field-descriptor name="abc" column="ABC" primarykey="true" jdbc-type="INTEGER"/>
  <reference-descriptor name="child" class-ref="child">
   <foreignkey field-ref="parent2Id" />
   <foreignkey field-ref="abc" />
  </reference-descriptor>

</class-descriptor>

When storing the child table, it is possible that data from only one of the parent tables is populated.
When I am trying to store in that database, I dont have a problem when I call the set method on one of the parent objects of the child but when I try to refer the second parent in the same manner, I am getting a sql exception that says that data does not exist in the parent table and the transaction is being rolled back(The parent object is stored in the database first...). I have tried removing the OJB relation and manually setting the foreign key fields for the second parent. In this case I am able to store the part of the key that is not shared but the shared part is being set to null in the database. I have also tried to make one of them a collection-descriptor. I got the same sql exception. I am not sure if there is a problem with how the database is setup or how my OJB mappings are and classes are setup. At this point, short of making a database change, I feel I have exhausted all available option. Am I missing something? Any advice would be appreciated..

Thanks,
-Sri.





 
____________________________________________________________________________________
Any questions? Get answers on any topic at www.Answers.yahoo.com.  Try it now.