We have a table which refers to prospective customers (called Prospect). Each prospective customer is in only one Campaign (many-to-one). Each prospective customer also has two keys, purl_key and external_key.

These keys are both unique within a Campaign. i.e. we would like the SQL to be



However, we can't see how to use the unique-key attribute twice on the campaign column -- it seems that you can only have one unique-key attribute on a single column. Is this true?

Here are the details:

Hibernate version:
2.1. (Is there a version number in the hibernate2.jar anywhere? If so, where? If not, can one be added?)
Mapping documents:


Name and version of the database you are using:
MySQL 4.0.17

The mapping above sets up only the unique constraint on (campaign, purl_key). That works fine. But we can't set up another unique constraint on (campaign, external_key).

We have tried the following mapping variations:




Neither works.

Is there any way to do this with the 2.1 release (or any other release) of hbm2ddl? And if so, what should the mapping look like?

Thanks very much!
Cheers,
Rob

A coworker suggested adding a second "pseudo-property" mapping to the same campaign:

[code] <!-- Prospects know their Campaign in order to access their Campaign's custom prospect schema -->
<many-to-one name="campaign" class="com.nimblefish.core.domain.campaign.Campaign">
<column name="campaign" unique-key="campaignPurl_key"/>
</many-to-one>

<!-- We double map this column solely to get the two unique constraints on it. -->
<many-to-one name="campaign_private" class="com.nimblefish.core.domain.campaign.Campaign">
<meta attribute="scope-get">private</meta>
<meta attribute="scope-set">private</meta>
<column name="campaign" unique-key="campaignExternal_key"/>
</many-to-one>

<!-- purl keys must be unique in Campaign! -->
<property name="purl_key" type="string">
<meta attribute="scope-get">public</meta>
<meta attribute="scope-set">public</meta>
<column name="purl_key" length="50" unique-key="campaignPurl_key"/>
</property>

<!-- external_keys must be unique in Campaign! -->
<property name="external_key" type="string">
<meta attribute="scope-get">public</meta>
<meta attribute="scope-set">public</meta>
<column name="external_key" length="50" unique-key="campaignExternal_key"/>
</property>[code]

This works :-) Generates exactly the SQL we want. But it's a bit grotty. Is there a cleaner way? At this point a cleaner way would just be a minor bonus....

Shall we file a JIRA? Is this at the level of feature requests you all have the patience to bother with? ;-)

Thanks for the wonderfulness that is Hibernate.
Cheers!!!
Rob

Hmm, dunno why the code tag didn't work above.

Anyway, one last correction:



You have to have the insert="false" update="false" on the double-mapped column. (What an excellent warning THAT is! Thanks to Hibernate itself for complaining so loudly about it :-D )

Cheers!
Rob

hum, yes, this is an annoying limitation, that you can't have a single property in two indexes. submit it to JIRA, it is not very good.

What should the syntax be? i.e. of my two failed examples in my first post, which syntax is preferable? Or should it be expressed in a yet different way?

One thing's for sure, you can't have two unique-key attributes on the same column :-)

Cheers!
Rob