Unique constraint on multiple columns (combined)

[aravind yarram]

Hi all,

I want to add a unique constraint on multiple columns. Can someone please let me know how to do specify this in the mapping document (if at all we can). Also, how does hibernate report the unqiue constraint violation? Does it give the name of the constraint that has been violated?

I want the unique constraint to be on "csaType" and "equipmentDeliverDate" columns combined!

Thank you in advacne!

Hibernate version: 3.x

Mapping documents:

<hibernate-mapping>

<class name="Csa" table="CSA">

<id name="id" column="CSA_ID">
<generator class="native"/>
</id>
<property name="csaType" column="CSA_TYPE" type="string" not-null="true" length="2"/>
<property name="detailedCsaType" column="CSA_TYPE_DETAIL" type="string" not-null="true" length="20"/>
<property name="startingSmu" column="STARTING_SMU" type="double" not-null="true" precision="10" scale="2"/>
<property name="smuIndicator" column="SMU_INDICATOR" type="string" not-null="true" length="2"/>
<property name="equipmentDeliveryDate" column="EQUIP_DELIVERY_DATE" type="date" not-null="true" />
<property name="csaStartDate" column="CSA_START_DATE" type="date" not-null="true" />
<property name="csaEndDate" column="CSA_END_DATE" type="date" not-null="true" />

</class>

</hibernate-mapping>

Name and version of the database you are using:Oracle 10g

[Arun Kumarr]

Write a component class called MyUniqueClass (say) and have getters and setters for the two properties csaType and equipmentDeliveryDate

  <component name="myunique" class="MyUniqueClass" unique="true">
<property name="csaType" column="CSA_TYPE" type="string" not-null="true" length="2"/>
<property name="equipmentDeliveryDate" column="CSA_TYPE_DETAIL" type="string" not-null="true" length="20"/>
</component>  

Now, you can use it as a unique id.

[Arun Kumarr]

Supposing you are trying to persist a single CSA POJO, unless hibernate hits the DB, it will never be able to find out whether the combination you are sending is unique or not.

[pascal betz]

Component ? Why make a component out of this ? How do you ensure uniqueness in DB then ?
It's just about a Database constraint. No need to mess with your Object Model for this.
- You can add the constraint to the database manually (after you created the schema from the hbm files)
- You can create a natural identifier which is also useful for caching when doing lookup trough the compound key. check http://www.hibernate.org/hib_docs/v3/reference/en/html/querycriteria.html (scroll down to 15.9). i think hibernate will create a unique constraint for this.


pascal

[pascal betz]

forgot:
with Hibernate Annotations you can specify something like

@Entity
@Table (name="bla", uniqueConstraints = {@UniqueConstraint(columnNames= {"first", "second"})})

and so on.

Dont know if you can do this with the hbm files directly.

pascal

[Arun Kumarr]

Originally posted by pascal betz:
Component ? Why make a component out of this ? How do you ensure uniqueness in DB then ?

pascal

That's exactly what I said,

Supposing you are trying to persist a single CSA POJO, unless hibernate hits the DB, it will never be able to find out whether the combination you are sending is unique or not.

But on second thought consider this scenario. Say we have a set of CSA POJOs. If two objects in the set have the same MyUniqueClass component won't hibernate throw an error, before even hitting the DB ?
[ August 11, 2006: Message edited by: Arun Kumarr ]

[pascal betz]

The unique="true" is only for the DB. It is not checked by Hibernate in memory. Hibernate has no possibility to tell if a component is unique unless it checks them all -> hitting the DB. And if Hibernate needs to hit the DB, then it can also delegate the job to the DB :-)

I think messing with the object model just to create a unique constraint in the DB is a bit complicated. Rather add the constraint manualy (with DB specif DDL :-( ) and work with the Object model that fits.

As mentioned the natural key element also creates a unique constraint. Problem here is that a column/property can only be in one unique constraint. If you have columns A, B and C then you can not create unique constraints A/B and B/C with this aproach. I don't know if there is a solution for this.


pascal

[Arun Kumarr]

If two objects in the set have the same MyUniqueClass component won't hibernate throw an error, before even hitting the DB ?

Hibernate doesn't.

That derives that the unique="true" is only used when you do a hbm2ddl conversion.
Ideally, shouldn't the Hibernate ORM mapping reflect the Database mapping.
Consider a scenario, where there is a collection of objects(Set s) and we do a saveOrUpdateAll(Set s), shouldn't the hibernate have some way which checks for the unique constraint before hitting the DB?

Here Will checking for this condition take more time than merely cascading it to the DB and then let it throw a unique constraint violation.
I believe one of the reasons of ORM layer Hibernate was to ensure the integrity of the POJOs before hitting the DB since Database connections could be very costly.

[pascal betz]

Yes, as mentioned, the unique="true" attribute is only for DB (hbm to DDL).


Hibernate could check not-null constraints but it CAN NOT check the constraints because it does not know all the data...
How should Hibernate be able to check a unique constraint without checking the DB ? it would need to keep all the data in memory! It is not the job of hibernate that is what the DB is good for.


pascal

[Arun Kumarr]

Originally posted by pascal betz:
Hibernate could check not-null constraints but it CAN NOT check the constraints because it does not know all the data...
How should Hibernate be able to check a unique constraint without checking the DB ? it would need to keep all the data in memory! It is not the job of hibernate that is what the DB is good for.


pascal

Iam talking about a collection of POJOs, which has two column values which is marked as unique but has same value. It need not hit the DB for that.