Re: Problem with multiple composite keys

Hi Srilakshmi,

Srilakshmi Machineni wrote:
> Armin, Thanks for your response. I am not sure if this is a problem
> with the bidirectional reference. We have these references throughout
> the application. We store the child in two steps but its the same
> error. As of now we first store the child table without references
> and then store the parent table and then the reference to the parent
> table in the child table. Could sharing of the composite keys be a
> problem?

Oh, now I got it. You are right, the problem is the shared FK 'abc' - 
one will always override the other. Has 'abc' always the same value for 
both parent references?

regards,
Armin

> 
> Thanks Sri.
> 
> ----- Original Message ---- From: Armin Waibel <[email protected]> 
> To: OJB Users List <[email protected]> Sent: Wednesday, February
> 14, 2007 11:40:04 AM Subject: 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]
>> 
>> 
> 
> 
> ---------------------------------------------------------------------
>  To unsubscribe, e-mail: [email protected] For
> additional commands, e-mail: [email protected]
> 
> 
> 
> ____________________________________________________________________________________
>  Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail
> beta. http://new.mail.yahoo.com
> 
> ---------------------------------------------------------------------
>  To unsubscribe, e-mail: [email protected] For
> additional commands, e-mail: [email protected]
> 
>