Problem with multiple composite keys

[Srilakshmi Machineni <ma...@yahoo.com>]

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: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org

Re: Problem with multiple composite keys

[Armin Waibel <ar...@apache.org>]

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: ojb-user-unsubscribe@db.apache.org For
> additional commands, e-mail: ojb-user-help@db.apache.org
> 
> 


---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org