Re: Problem with multiple composite keys

Hi,

could this problem be caused by the bidirectional references? How do you 
store the child object? If you store the object in two steps does this 
prevent the error:

broker.beginTransaction();
// store child without references
broker.store(child);
// now set the references
child.setParent(p1)
p1.setChild(c);
...
// update child
broker.store(child);
broker.commitTransaction();

regards,
Armin

Srilakshmi Machineni wrote:
> 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.
> 
> 
> 
> ____________________________________________________________________________________
>  TV dinner still cooling? Check out "Tonight's Picks" on Yahoo! TV. 
> http://tv.yahoo.com/
> 
> ---------------------------------------------------------------------
>  To unsubscribe, e-mail: [email protected] For
> additional commands, e-mail: [email protected]
> 
>