Hibernate version: 3.2.0
DB: Oracle 9

I have a table with a surrogate key as well as a natural key. The natural key has a uniqieness constraint. I find that when using hibernate I often get a unique constraint error.

My question: is there some way to make hibernate check whether the unique key exists so that rather than doing an insert when I saveOrUpdate it will do a select and use the current surrogate key if a record exists with the unique key?

I have tried all combinations of <natural-id> as well as the unique-key attribute to no avail.

If there is not an automatic way to handle this, what is the best way?

Here is a snippet of mapping document to show what I mean:

Since you have sequence generator you should not be getting the unique key constraint error.
Add the Select-before-update for your mapping file.




My Forte 4GL and Spring Blog

srigold

I realise I haven't described my scenario in enough detail. select-before-update doesn't help in my case because I don't have the actual surrogate key value when I am creating it.

This is the scenario:
- I receive some data for the first time, grpNumber=1, sCode=A, jCode=B
- do a saveOrUpdate() -> hibernate does an insert. All is well.
- I receive some data again. The natural key elements are the same, i.e. grpNumber=1, sCode=A, jCode=B
- do a saveOrUpdate() -> hibernate does an insert because the primary key field is null.
-> this results in a "Unique Constraint violated" error from Oracle.

Now, I know that I can do a query first to see if there is a matching enrty and then just do an update. But I was hoping that there was a way to get hibernate to do a select first to check the natural key.

In this example with only a single table it is not a big deal, but I have more complex examples that would involve walking through a variety of child objects to perform the necessary checks.

Try custom SQL to merge data (see SQL MERGE statement)

Maybe I am missing something here, but it seems like using a surrogate key while also having a unique natural key is a fairly normal situation and there should be a straight forward way to handle it?

If I had created a composite key instead of using a surrogate key, hibernate would handle this situation for me. Surely there is a way to get hibernate to do a select on the natural key for me without having to do it manually?