one-to-many association references on none primary key

hugebrush · **Joined:** Fri Mar 03, 2006 5:00 am **Posts:** 1

How do map one-to-many association references on none primary key?

For example, two tables:

Code:

PRODUCTS(ID, NAME, SN)
   PARTS(ID, NAME, PRODUCT_SN)

PRODUCTS(ID) and PARTS(ID) are primary keys, PARTS(PRODUCT_SN) references on none primary key PRODUCTS(SN).

How to map Product.Parts below to the two table above?

Code:

public class Part{
    public int Id {
       get{...}
       set{...}
    }

   public int Name{
      get{...}
      set{...}
   }
}

public class Product{

   //Set of Part Objects
   public ISet Parts{
       get{...}
       set{...}
   }

  public String SN{
       get{...}
       set{...}
  }
   ...
}

ivolved · **Joined:** Fri Nov 11, 2005 1:04 pm **Posts:** 22

I'd like to know the answer to this too. I tried using the foreign-key attribute on the key element to no avail. A little help in this area (even if it's a "you'll have to do it manually") would be appreciated.

Michael

ivolved · **Joined:** Fri Nov 11, 2005 1:04 pm **Posts:** 22

Still waiting for an answer here.

samujob · **Joined:** Tue Jan 03, 2006 7:21 am **Posts:** 85

I am not sure if this will work but please try this in the Product mapping:

Code:

<set name="Parts">
<key column="SN" />
<one-to-many class="Part" property-ref="ProductSN/>
</set>

I am not sure if property-ref works for one-to-many association. but try it out

ivolved · **Joined:** Fri Nov 11, 2005 1:04 pm **Posts:** 22

samujob wrote:

I am not sure if this will work but please try this in the Product mapping:

Code:

<set name="Parts">
<key column="SN" />
<one-to-many class="Part" property-ref="ProductSN/>
</set>

I am not sure if property-ref works for one-to-many association. but try it out

Nope doesn't work. Invalid attribute.

bladefenix · **Joined:** Tue Mar 14, 2006 10:02 pm **Posts:** 2

I assume that hibernate can't support this type of foreign key reference.
You should always reference to the primery key.
You have to reference to the key other than primary key because your relation model is not normal form.
Redesign it may be a good solution.

ivolved · **Joined:** Fri Nov 11, 2005 1:04 pm **Posts:** 22

bladefenix wrote:

I assume that hibernate can't support this type of foreign key reference.
You should always reference to the primery key.
You have to reference to the key other than primary key because your relation model is not normal form.
Redesign it may be a good solution.

Believe you me that my database schema passes the test for 4th normal form.

The reason one of my tables has a foreign key that isn't the primary key of the other table is because I chose not to use a column that has business relevance as a primary key (a definite no-no). However, the second table is imported from a flatfile and when new records are imported, they will not always have a matching record in the first table. This would cause a foreign reference violation.

The problem isn't my table design, the problem is that nHibernate doesn't support a common phenomenon in database structure. Either that or I don't know how to utilize this functionality.

samujob · **Joined:** Tue Jan 03, 2006 7:21 am **Posts:** 85

I think NHibernate does not support this feature.

After some digging I found that this has been included in the Hibernate 3.0 (Jave version).
It says:
"collection keys may now specify a property-ref, allowing mapping of associations that do not reference primary keys"

I think this is what we need in NHibernate as well. I agree this is a necessary feature.

languagegame · **Joined:** Fri Apr 06, 2007 2:17 pm **Posts:** 2

If it helps, I was able to create the inverse relationship (many-to-one) using the property-ref attribute.

First, I added a Product property to the Part object. This contained a reference to the parent Product object.

Then I added the following code to the part object mapping file:
<many-to-one name="Product" column="PRODUCT_SN" class="Product" property-ref="SN"/>

This allowed me to navigate from the Part object back to the parent Product object.

I know you were looking for a one-to-many relationship, but perhaps this helps.

marcal · **Posted:** Mon Sep 17, 2007 4:03 pm

I've just run up against this one myself while trying to map to a legacy database. This does seem like an obvious and necessary feature, owing to the sometimes odd thinking of DBAs (no offense ivolved). Nonetheless, I couldn't seem to find a request for this in JIRA, either as a new or improved feature. I'm adding it now.

Just for reference, the foreign-key attribute is for adding foreign key constraints as part of schema generation (hbm2ddl), where applicable.

Added http://jira.nhibernate.org/browse/NH-1134

markalroberts · **Joined:** Thu May 31, 2007 1:19 pm **Posts:** 23

Unfortunately, creating a new app that need to map onto an existing schema, hit this problem (specifically where type(property-ref) != type(pk))

I've nobbled the error check for now and it seems to work ok (only using it as read-only for now, so could hit problems in update).

I've had a look at the code, but it's a little unclear to me the implications of having the propert-ref column type different - am I letting myself in for lots problems by removing the isassignable from check?