must have same number of columns as the referenced primary k

kum · **Joined:** Wed Aug 31, 2005 8:18 pm **Posts:** 18

Hi,

I have two tables product and client. In PRODUCT table i have a composite key(combination of CUST_NBR and PCODE) which references the CR_NBR of the CLIENT table.
I get the following error:
org.hibernate.MappingException: Foreign key (FK7F3752F6EC720970:CLIENT [CR_NBR])) must have same number of columns as the referenced primary key (PRODUCT [CUST_NBR,PCODE])

the following are my mapping files:

product.hbm.xml

<hibernate-mapping>
<class name="com.abc.entities.product"
table="PRODUCT"
lazy="false">

<composite-id name="customerPK" class="com.abc.entities.CustomerPK" >
<key-property name="customerNumber" type="string" column="CUST_NBR">
</key-property>
<key-property name="pCode" type="string" column="PCODE" >
</key-property>
</composite-id>

<property name="amount" type="double" column="AMT" not-null="true"/>
<set name="clients" table="CLIENT" >
<key>
<column name="CR_NBR" />
</key>
<one-to-many class="com.abc.entities.Client"/>
</set>

</class>
</hibernate-mapping>

client.hbm.xml

<hibernate-mapping>

<class name="com.abc.Client"
table="CLIENT"
lazy="false">

<id name="clientNumber" type="string" column="CLIENT_NBR">
</id>

<property name="clientReferenceNumber" type="string" column="CR_NBR" not-null="true"/>

<many-to-one
name="product"
class="com.abc.entities.Product"
not-null="true"
update="false"
insert="false">
<column name="CR_NBR"/>
</many-to-one>
</class>
</hibernate-mapping>

Any help in solving this problem would be greatly appreciated.

Thanks,
Kum

ErikFK · **Posted:** Thu Sep 08, 2005 11:03 pm

Hi,

Your mapping is wrong.

Instead of

Code:

 <many-to-one
name="product"
class="com.abc.entities.Product"
not-null="true"
update="false"
insert="false">
<column name="CR_NBR"/>
</many-to-one> 

you should have written something like

Code:

 <many-to-one
name="product"
class="com.abc.entities.Product"
not-null="true"
update="false"
insert="false">
<column name="CR_NBR"/>
<column name="PCODE"/>
</many-to-one> 

Reason: you must refer to the complete foreign key in your association.

Erik

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Or you could use a property-ref tag to specify what the foreign key is. If you're using the default reference (like your mappping does) it must be to the primary key.

kum · **Joined:** Wed Aug 31, 2005 8:18 pm **Posts:** 18

Hi,

I still get the same error.
I am trying to map the composite id (CUST_NBR ,PCODE) in PRODUCT table with a single column(CR_NBR) in CLIENT. The relationship between PRODUCT and CLIENT is one-to-many.
Is it possible to do this, any ideas please?

Thanks,
Kum

kum · **Joined:** Wed Aug 31, 2005 8:18 pm **Posts:** 18

any ideas to solve this problem would be greatly appreciated.
I tried differnt ways but i still get the same error:
Foreign key (FK7F3752F6EC720970:CLIENT [CR_NBR])) must have same number of columns as the referenced primary key (PRODUCT [CUST_NBR,PCODE]

ErikFK · **Posted:** Fri Sep 09, 2005 3:07 pm

Hi Kum,

Somehow I'm confused. What do you mean by

Quote:

I am trying to map the composite id (CUST_NBR ,PCODE) in PRODUCT table with a single column(CR_NBR) in CLIENT

?
Are you trying to map existing tables? If yes, could you post the SQL create statement for the tables?
If you are starting from classes, could you post the classes?

Give us more details - so that we can help you.

Erik

kum · **Joined:** Wed Aug 31, 2005 8:18 pm **Posts:** 18

ErikFK wrote:

Hi Kum,

Somehow I'm confused. What do you mean by

Quote:

I am trying to map the composite id (CUST_NBR ,PCODE) in PRODUCT table with a single column(CR_NBR) in CLIENT

?
Are you trying to map existing tables? If yes, could you post the SQL create statement for the tables?
Erik

The following are the table descriptions:

Product:
CUST_NBR pk
PCODE
PTYPE

CLIENT:
CLIENT_NBR pk
CR_NBR

The relationship is one to many between product and client. the combination of CUST_NBR and PCODE in PRODUCT is equivalent to CR_NBR in CLIENT.
In my product.hbm.xml, i used the compsite id so that i can refer to both CUST_NBR and PCODE as a single entity.

In my action class, iam using the following query
query = session.createQuery("from com.abc.entities.Product");

kum · **Joined:** Wed Aug 31, 2005 8:18 pm **Posts:** 18

ErikFK wrote:

Hi Kum,

Somehow I'm confused. What do you mean by

Quote:

I am trying to map the composite id (CUST_NBR ,PCODE) in PRODUCT table with a single column(CR_NBR) in CLIENT

?
Are you trying to map existing tables? If yes, could you post the SQL create statement for the tables?
Erik

The following are the table descriptions:

Product:
CUST_NBR pk
PCODE
PTYPE

CLIENT:
CLIENT_NBR pk
CR_NBR

The relationship is one to many between product and client. the combination of CUST_NBR and PCODE in PRODUCT is equivalent to CR_NBR in CLIENT.
In my product.hbm.xml, i used the compsite id so that i can refer to both CUST_NBR and PCODE as a single entity.

In my action class, iam using the following query
query = session.createQuery("from com.abc.entities.Product");

ErikFK · **Posted:** Fri Sep 09, 2005 5:12 pm

Hi Kum,

I would expect the database tables in a one to many between Product and Client to look like this:

Code:

Product:
CUST_NBR pk
PCODE pk
PTYPE

Client:
CLIENT_NBR pk
CR_NBR
FK_CUST_NBR
FK_PCODE

FK_CUST_NBR and FK_PCODE would be foreign keys to Product - and Product would have both CUST_NBR and PCODE form the primary key. This way I can navigate from one Client to one Product and many client can be associated to one product.
This looks sensibly different from what you posted - so I must be misunderstanding you somewhere.

If you put CUST_NBR and PCODE in a composite id, it means (for me - and I think also for Hibernate) that those both columns form Product's primary key. But you only declared CUST_NBR as pk for Product - was that correct?

And I'm currently unable to understand what you mean when you say

kum wrote:

the combination of CUST_NBR and PCODE in PRODUCT is equivalent to CR_NBR in CLIENT

What do you mean with equivalent? Is there a formula allowing to compute CR_NBR from CUST_NBR and PCODE (and vice versa)?

I'm probably overseing something simple in your explanation - but I can't figure out what it is.

So I must ask you to be as detailled as you can in describing your requirements - what seems obvious to you who are fighting that problem for a while now, is not obvious for someone far away (at least not for me) - and to provide all the code you have related to this issue.

Erik

kum · **Joined:** Wed Aug 31, 2005 8:18 pm **Posts:** 18

Hi Eric,

First of all thanks for trying to help me.

I am dealing with a legacy database, which has two tables:
PRODUCT
CUST_NBR pk
PCODE
PTYPE

CLIENT:
CLIENT_NBR pk
CR_NBR

These two tables don't have any common field. But CR_NBR(field in CLIENT table) is a concatenation of CUST_NBR(field in PRODUCT table) and PCODE (field in PRODUCT table).
My requirement is i have to display records from both tables where CUST_NBR||PCODE=CR_NBR

I did not know to do concatenation of two fields in mapping for this requirment in Hibernate. Then i came across composite ID concept and tried to put CUST_NBR and PCODE in it, so that i can access both fields as a single entity

Please let me know, am i in the right direction.

Thanks,
Kum

Jason Zhicheng Li · **Joined:** Tue Aug 16, 2005 11:06 pm **Posts:** 46

I would suggest you use Custom value types (UserType or CompositeUserType).

kum · **Joined:** Wed Aug 31, 2005 8:18 pm **Posts:** 18

I tried using the Composite User Type, but still i get the same error.
I would appreciate, if some one helps me in solving the problem.

ErikFK · **Posted:** Mon Sep 12, 2005 11:18 pm

Hi Kum,

I tried quite a lot, but found no elegant solution. My feeling is that you need to program this out "by hand", that is that you have to look for the product associated to a client and to the clients associated to a product by writing the SQL statements yourself.

I'm in a hurry now, but I'll try to post you some code later showing you what I mean. Maybe someone will provide you a smarter solution in the meantime...

Erik

ErikFK · **Posted:** Tue Sep 13, 2005 1:21 am

Hi Kum,

I'm back to it again. The code I would use is a follows:

Suggested Mapping (simplified!):

Code:

<class name="Product" table="PRODUCT">
    <id name="customerNumber" type="string" column="CUST_NBR" >
        <generator class="assigned" />
    </id>
    <property name="pCode" column="PCODE" />
</class>

<class name="Client" table="CLIENT">
    <id name="clientNumber" type="string" column="CLIENT_NBR">
    </id>
    <property name="clientReferenceNumber" type="string" column="CR_NBR"/>
</class>

The classes (also simplified):

Code:

public class Client {
    private String clientNumber;
    private Product product;
    private String clientReferenceNumber;
    // Add getter and setters + constructor(s)
}

public class Product {
    private String customerNumber;
    private String pCode;
    private Set clients = new HashSet();
    // Add getter and setters + constructor(s)
}

In order to get a set of products with their "associated" clients (note that the association between client and product is also populated):

Code:

Query query = session.createQuery("SELECT c, p "
    + "FROM Product p, Client c WHERE c.clientReferenceNumber = (p.customerNumber || p.pCode)");
List list = query.list();
Iterator it = list.iterator();
Set productSet = new HashSet();
while(it.hasNext()) {
    Object[] objects = (Object[]) it.next();
    Client c = (Client) objects[0];

    Product p = (Product) objects[1];
    c.setProduct(p);
    p.getClients().add(c);
    productSet.add(p);
}

return productSet;

I must admit that it's a bit "ugly" - but as I said, I didn't find a "nicer" way. And in the meantime I don't think that using an association is the right way.

Note: I didn't test the code above a lot - so there may be a few mistakes left. Analyse it - and decide whether it is useful or not...

Erik

kum · **Joined:** Wed Aug 31, 2005 8:18 pm **Posts:** 18

Hi Erik,

I tried ur code, but i get the following error:
ORA-01002: fetch out of sequence.

Thanks,
Kum