Hi guys, i'm facing a problem since 2 weeks.
I've two tables which have no relationship between them.

Table Account_Master(AccountNumber, MarketId, AccountType) where AccountNumber and MarketId are composite keys.

Table Call_Reason(AccountNumber, CallDate, CallReason, CallDesc) where AccountNumber and CallDate are composite keys.

I want to map a relationship between these tables based on AccountNumber.

Here are the mapping files.

Account_Master hbm file:

<class name="com.hibernate.pojos.AccountMaster" table="ACCOUNT_MASTER" schema="dbo" catalog="CNCHdb">
<composite-id name="id" class="com.hibernate.pojos.AccountMasterId">
<key-property name="marketId" type="string">
<column name="MarketId" length="5" />
</key-property>
<key-property name="accountNumber" type="integer">
<column name="AccountNumber" />
</key-property>
</composite-id>
<set name="callReasons" inverse="true" cascade="none" table="CALL_REASON" >
<key>
<column name="AccountNumber" not-null="true" />
</key>
<one-to-many class="com.hibernate.pojos.CallReason" />
</set>
</class>

Call_Reason hbm file:
<class name="com.hibernate.pojos.CallReason" table="CALL_REASON" schema="dbo" catalog="CNCHdb">
<composite-id name="id" class="com.hibernate.pojos.CallReasonId">
<key-property name="accountNumber" type="integer">
<column name="AccountNumber" />
</key-property>
<key-property name="callReasonDate" type="timestamp">
<column name="CallReasonDate" />
</key-property>
</composite-id>

<property name="callReasonNote" type="string">
<column name="CallReasonNote" not-null="true" />
</property>

<many-to-one name="accountmaster" class="com.hibernate.pojos.accountmaster" update="false" insert="false" fetch="select">
<column name="accountnumber" not-null="true" />
</many-to-one>

</class>

but i'm getting the error as:
org.hibernate.MappingException: Foreign key (FKAF34B45F2230FD:CALL_REASON [AccountNumber])) must have same number of columns as the referenced primary key (ACCOUNT_MASTER [MarketId,AccountNumber])
at org.hibernate.mapping.ForeignKey.alignColumns(ForeignKey.java:90)
at org.hibernate.mapping.ForeignKey.alignColumns(ForeignKey.java:73)
at org.hibernate.cfg.Configuration.secondPassCompileForeignKeys(Configuration.java:1145)
at org.hibernate.cfg.Configuration.secondPassCompile(Configuration.java:1052)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1168)

Can anybody correct if i went wrong.
Please post me the as soon as possible.
Thanks in advance.

You cannot map a relation to a table without specifying the whole primary key columns.

If you have a table A(c1,c2,c3) with primary_key(C1,C2) all tables referencing A will have to have TWO columns to reference both foreign columns.

This is a relational rule, not an hibernate one.

But we have a database desing like that. We have two tables with the below given situation. Thats why i posted the issue. If at all we get that type of situation how can we handle.

But we have a database desing like that. We have two tables with the below given situation. Thats why i posted the issue. If at all we get that type of situation how can we handle.

You must specify primay and foreign key constraints in order for Hibernate to see the relationship that is implied.

Hibernate can't guess at a relationship between tables. I had the same problem recently with a legacy DB that our client requires that we use.

What we did was go through the DB, specify constraints, then generate Hibernate mappings. There are some potentail hicups and some places in the DB that aren't easily reached, but thats the downfall of using new technology like Hibernate on an old poorly designed DB.

Can you please update me the xml mappings files which i have posted.
Its very urgent.

Can you please update me the xml mappings files which i have posted.
Its very urgent.