FK based on unique constraint (instead of PK) problem

stalamoni · **Joined:** Fri Jul 29, 2005 10:34 am **Posts:** 25

Hibernate version: 1.0.2 (also tested with 1.2 Alpha)

Hi,

I can't find anything in the documentation that will explain / deny
if a FK based on a unique key (alternative key, not the a Primary Key)
is supported or not for bi-directional associations.

I was able to find this comment from Gavin (go to the end):
http://forum.hibernate.org/viewtopic.ph ... criminator
where basically he says it's not supported but i don;t understand why this restriction exists.

Example:

TableA
--> f1 (pk)
--> f2 (alternative unique key/constraint)

TableB
--> f3 (pk)
--> f2* (fk --> TableA)

Currently i thought that this would be the correct mapping:

TableB.xml

Code:

  <many-to-one name = "TableA"   class = "TableA"   property-ref= "f2"  >
   <column name="`f2*`" />
  </many-to-one>

This works OK.
But when i try to make this a bi-directional --> problems.

TableA.xml

Code:

<bag name="TableA"  lazy="true"  inverse="true" >
   <key>
     <column name= "`f2`" />  
   </key>
   <one-to-many class="TableB" />
</bag>

The error is the following:

collection foreign key mapping has wrong number of columns: BO_Layer.SupplierBooks.PriceHistory type: SupplierBooks

Note: I've also tested with Nhibernate 1.2 Alpha and i get the same behaviour.

Thanks in advance,
Sebastian

GeAr · **Posted:** Fri Sep 01, 2006 8:30 am

Hi Sebastian,

I was going to ask if you knew about the property-ref attribute, and then I saw it already was there. Sorry. Anyway, are the column names in your mapping files correct or were they a bit distorted by copy/paste?

stalamoni · **Joined:** Fri Jul 29, 2005 10:34 am **Posts:** 25

GeAr wrote:

Hi Sebastian,

Do you know about the property-ref attribute (NHibernate doc ch. 5.1.10). This is meant for 'data where a foreign key refers to a unique key of the associated table other than the primary key'.

Hope this helps.

Thanks for the fast response, actually if you look in the posted code that's what i am already using.

Actually, the many:1 association works fine. My problem is the bag (many side) that does not pay attention the related table defined links (columns) but always checks and forces the use of it's own PK for the Where clause (thus errros since the column (PK) of the parent table don't exist in the child).

Sebastian

jokiz · **Joined:** Wed Mar 29, 2006 2:24 am **Posts:** 14

this issue of property-ref of bags is killing me, it's still joining with the id and not the property-ref property

anuarneto · **Joined:** Fri Oct 20, 2006 8:02 am **Posts:** 36

try adding the foreing-key attribut to your key into the bag.

Code:

<bag name="TableA"  lazy="true"  inverse="true" >
   <key foreing-key="nome da propriedade referenciada" >
     <column name= "`f2`" /> 
   </key>
   <one-to-many class="TableB" />
</bag> 

stalamoni · **Joined:** Fri Jul 29, 2005 10:34 am **Posts:** 25

anuarneto wrote:

try adding the foreing-key attribut to your key into the bag.

Thanks for the reply. Actually i've tried that and the problem is that it uses the correct column (CountryCode) but it uses the ID instead for the value (so the column/property is correctly taken but the value is not from CountryCode but from CountryID instead).

This is my mapping for the Countries Table:

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0" 
 namespace="BO_Layer" 
 assembly ="BO_Layer" >
  <class name = "Countries" 
   table= "[dbo].[Countries]" 
>
    <id name="countryID"
     column="`CountryID`"
     type="System.Int32"
    >
      <generator class="assigned" />
    </id>
    
    <property
    name="countryCode"
    column="`CountryCode`"
    type="System.String"
    length="3"
    >
    </property>
    
    <property
    name="name"
    column="`Name`"
    type="System.String"
    length="12"
    >
    </property>
    
    <bag name="members" cascade="all-delete-orphan" lazy="false"  inverse="true" >
      <key foreign-key="countryCode"></key>
      <one-to-many class="Members" />
    </bag>
  </class>
</hibernate-mapping>

And for the Members table:

 <property
    name="countryCode"
    column="`CountryCode`"
    type="System.String"
    length="3"
    >
    </property>
    <many-to-one
     name = "countries" 
     class = "Countries" 
     property-ref= "countryCode" 
     cascade = "save-update" 
     outer-join = "auto" 
     insert = "false" 
     update = "false" 
>
      <column name="`CountryCode`" />
    </many-to-one>

Here is the generated SQL :
SELECT members0_.CountryCode as CountryC8___, members0_.[Member_id] as y1___, members0_.[Member_id] as y1_0_, members0_.[Member_Since] as y6_0_, members0_.[Zipcode] as y4_0_, members0_.[CountryCode] as y8_0_, members0_.[Name] as y2_0_, members0_.[Gender] as y7_0_, members0_.[Town] as y5_0_, members0_.[Street] as y3_0_ FROM [dbo].[Members] members0_ WHERE members0_.CountryCode=@p0
@p0 = '2'
---------------------------------------------

The value '2' is actually ContryID (int) where ContryCode is a string.

Note: Retested in 1.0.3 and i get the same behaviour.

jokiz · **Joined:** Wed Mar 29, 2006 2:24 am **Posts:** 14

i really couldn't make this work so i used the correct fk instead to avoid property-ref which is i think is problematic

riz · **Joined:** Fri Dec 01, 2006 11:36 am **Posts:** 6

i have run into the same problem. Has anyone found a solution?

sthorogood · **Joined:** Mon Jul 18, 2005 7:45 am **Posts:** 16

I've also encountered this issue and not been able to find a satisfactory solution.

This occurs when we are forced to work with an existing production database which uses 'business keys' as primary keys. Adding a new identity database PK is acceptable (i.e. a non-breaking change) but where foreign keys are used they still reference the old business key column.

It seems odd that it is possible to create this type of mapping one way but not the other.

shinakuma · **Joined:** Wed Feb 07, 2007 11:17 am **Posts:** 5

Should this be filed as a bug? It is odd that the mapping is not recognized in both directions. Definitely not an expected behavior.

luca67 · **Posted:** Fri Feb 23, 2007 2:04 pm

Hi,
I'm in evaluating NHibernate and I like to start of use it in a real project but I get this problem and I spend several hours to find a reason of this topic, but I don't find a reasonable answer.

Just I can not use <one-to-many> mapping in my database. All my tables are designed to have a phisical primary key that is not used for foreign key (this is a normal and efficient pattern for relational db design).

Please someone has a reasonable answer ?

Other way to map logical foreign key in one-to-many exist ?

stalamoni · **Joined:** Fri Jul 29, 2005 10:34 am **Posts:** 25

shinakuma wrote:

Should this be filed as a bug? It is odd that the mapping is not recognized in both directions. Definitely not an expected behavior.

I think you are correct. I've just retested this issue with latest version 1.2 RC2 and i see the same behaviour.
I've tried to debug the code to find something in the code but i must say it's not so easy this one. :(

Update 20-04 : Before adding to JIRA i searched and found that it's already there: http://jira.nhibernate.org/browse/NH-892. I've another example that shows the problem in case anybody wants it.

Sebastian Talamoni

pvradhakrishna · **Joined:** Tue Feb 19, 2008 6:05 pm **Posts:** 82

Still have the problem with Hibernate 3.0

I have legacy tables with no keys (cannot ask them to add key's) and I need to map relationships with columns that are not primary keys!

Code:

Parent class
BigDecimal parentKey; (PK)
String someName; (used as FK in child)

Child class
BigDecimal childKey; (PK)
Parent parent; (FK referring to someName property in parent)

Parent table: FAILS IN FETCHING COLLECTION

Code:

<hibernate-mapping>
   <class name="Parent" >
      <id name="parentKey" type="big_decimal">
         <generator class="increment" />
      </id>
      
                <!-- not sure what property-ref is pointing to here, only parent object has this property and the child uses this column to create a parent object in the mapping file-->
      <set name="children" table="child">
         <key property-ref="someName" column="some_name"/>
         <one-to-many class="Child" />
      </set>
<!-- If I do not include the property ref it will join using the parent.parentKey = child.someName which is wrong and I want
parent.someName = child.parent.someName
-->
      
   </class>
</hibernate-mapping>

Child Mapping: WORKS IN FETCHING PARENT

Code:

<hibernate-mapping>
   <class name="Child">
      <id name="childKey" type="big_decimal">
      
       <!--property-ref here points a property in the parent object and works fine in bringing the parent by making the join on parent.planName = child.planName rather than on parent.parentKey -->

      <many-to-one name="parent" property-ref="someName"
            class="Parent">
            <column name="fk_col"></column>
      </many-to-one>
      
   </class>
</hibernate-mapping> 

End up getting
java.lang.ClassCastException: java.lang.String cannot be cast to java.math.BigDecimal