How to do multiple column UniqueConstraint in hbm?

Question

Working on some legacy hibernate code.

How do I do the following with hbm.xml(hibernate mapping file) instead of with annotations?

@Table(name="users", uniqueConstraints = {
    @UniqueConstraint(columnNames={"username", "client"}),
    @UniqueConstraint(columnNames={"email", "client"})
})
public class User implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    private int id;
    private String username;
    private String email;
    private Client client;
}

Estradiol

Answer 1

Use the properties tag:

...
<properties name="uk1" unique="true">
        <property name="username" .../>
        <many-to-one name="client" .../>
</properties>

<properties name="uk2" unique="true">
        <property name="email" .../>
        <many-to-one name="client" update="false" insert="false" .../>
</properties>
...

Documentation extract :

The <properties> element allows the definition of a named, logical grouping of the properties of a class. The most important use of the construct is that it allows a combination of properties to be the target of a property-ref. It is also a convenient way to define a multi-column unique constraint.

All available options are described in the Hibernate documentation.

Tightlipped

Hi, I know that that's what the documentation says, but that did not work for me. The solution was the put the unique inside the column tag instead of the property tag <property name="login" type="string"> <column length="45" name="Login" unique="true"/> </property> – Louralourdes

I don't know why the properties tag did not work for you (hibernate version ?) but your definition won't be able to model a multi-column unique constraint. – Tightlipped

it's Hibernate 3, and the solution I gave worked just as I wanted! – Louralourdes

Do we need hibernate-validator as well for this ?? – Shelley

@Shelley : I don't think so. It should be a hibernate core feature – Tightlipped

@Tightlipped I am using postgreSQL with hibernate and when I used the snippet you mentioned, hibernate wouldnt "name" the constraint using the properties.name tag. Instead it would generate a random name and create the unique constraint. Any idea how you can specify the constraint name using your solution? – Kerry

Nop, sorry no idea. The hbm.xml configuration format is deprecated since a long time now. Also i'm guessing that you do not want to have predictable constraint name for testing environment, but for a more production like environment. The hibernate migration tool is really not advised in this case. Instead, have a look at liquid-base / flyway for a better way of handling migration of production data. – Tightlipped

Answer 2

You can also do this:

  <many-to-one name="client" unique-key="uk1,uk2" .../>
  <property name="username" unique-key="uk1"  .../>
  <property name="email" unique-key="uk2"  .../>

You do not need to use the tag in hbm . If you only want multiple unique constraints.

Substitute

Please see this first how-to-answer This question is answered before, obviously, you can add your answer here. But You Need to understand some points before answering. First, don't add an answer which is previously added with the same code or suggestion. Second, don't add an overly complicated answer if the user has asked very specifically about the problem and what he needs to solve this. Third, You can add a comment if you want to suggest anything regarding the answer or question. – Ariose

@ankitsuthar I am not entirely sure about your comment. First, he has added a different answer with a different suggestion (Atleast I could not find a similar answer in this particular question thread). Second, he has in-fact provided a simpler and specific answer to the problem "multiple column unique constraint in hbm". Third, since this is an alternative answer, I believe it shouldn't be a comment. – Kerry

This answer worked perfectly for me, I was not able to specify the "constraint name" using the accepted answer above. But using this answer, I got the constraint name as specified in the "unique-key" tag. – Kerry

Answer 3

You can add same unique-key attribute to two different columns. This will create composite unique key.

<property name="firstName" column="first_name" unique-key="name" />
<property name="lastName" column="last_name" unique-key="name" />

In the above example the unique key will be created from both first_name and last_name column.

Bindman