Hibernate version:
2.1

Name and version of the database you are using:
PostgreSQL 7.3.6

Question
I am trying to map a one-to-many relationship between two tables, but I need to use a different column in the parent table (ie not its primary key). Here's a sample mapping file:



The problem is the contained set isn't really a child table, and the key used here is not the primary key of the main table. So what I need to do is somehow specify a different column from the subscription_send_number table to use for the join (specifically, thread_id).

Is there any way to do this?

James

So the tables look something like:

SUBSCRIPTION_SEND_NUMBER
-------------------------------------
ID
THREAD_ID
...

MESSAGE
-------------------------------------
ID
THREAD_ID
...

Where MESSAGE.THREAD_ID references SUBSCRIPTION_SEND_NUMBER.THREAD_ID?

Yeah, the table schemas are a superset of that, basically.

BTW, there are other tables, like subscription_send_number, which will want to reference a collection of messages by their thread_id. That's what makes it complicated because these tables are the "client" of the message table and not the "parent" of it.

Yeah, so just setup the "other side" of the association using the <many-to-one/> element with the property-ref attribute.

I can't set up this relation at the other end because the thread id doesn't reference any table in particular. Only a small subset of the thread_ids in the message table will actually refer to the subscription_send_number table.

A little background info might explain...

This is a text messaging system, messages sent and received are stored in the message table. When sending a message you can either let it start a new thread (it generates a new thread_id, incidentally the same as the message_id) or you can give it an existing thread_id, in which case these messages are automatically linked. But this thread isn't a foreign key.

There are various modules built on top of this which send messages. The table here logs each subscriber who received a certain broadcast, and the thread_id allows us to find the actual messages sent (and then obtain delivery status etc...)

So basically, it makes no sense to try and do the mapping the other way because the message code knows nothing about the subscription system built on top of it.

You could think of it as a kind of many-to-many relationship without an intemediate table... I realise I could create a table to represent each thread and reference this with a one-to-many, or something similar, but this seems klunkly and against the whole feeling of Hibernate.

I imagine all you would need is to add another attribute to the <key ...> element to override the id column, and obviously whatever backend changes were necessary, assuming there isn't another way to do this.