• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Unique constraint on multiple columns (combined)

 
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 662
Eclipse IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Write a component class called MyUniqueClass (say) and have getters and setters for the two properties csaType and equipmentDeliveryDate



Now, you can use it as a unique id.
 
Arun Kumarr
Ranch Hand
Posts: 662
Eclipse IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 547
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 547
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 662
Eclipse IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Ranch Hand
Posts: 547
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 662
Eclipse IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Ranch Hand
Posts: 547
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 662
Eclipse IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic