Unique Constraint Over Multiple Columns

Question

I am using SEAM 2/Hibernate along with PostgreSQL 9 database. I have the following table

Active Band
===========
active_band_id serial
active_band_user text
active_band_date timestamp
active_band_process integer

I would like to add a constraint that ensures each new entry has a unique combination of active_band_user and active_band_date.

There could potentially be many attempted inserts per second so I need this to be as efficient as possible, is there a SEAM / hibernate annotation I can use in the entity mapping?

Thanks in advance

This appears to duplicate http://stackoverflow.com/questions/3211972/specifying-restrictions-unique-together-in-hibernate — Sarah Messer, Apr 19 '17 at 14:52

score 104 · Accepted Answer · answered Aug 14 '12 at 16:24

104

There is no Hibernate annotation that checks uniqueness before insert/update. But there is annotation which will generate such a constraint to database if automatic database creation is used:

 @Table(
    name="ACTIVE_BAND", 
    uniqueConstraints=
        @UniqueConstraint(columnNames={"active_band_user", "active_band_date"})
)

answered Aug 14 '12 at 16:24

Mikko Maunu

41,366
10
132
135

I dont think it supports this as I get 'The value for annotation attribute Table.uniqueConstraints must be some @javax.persistence.UniqueConstraint annotation' in Eclipse. Does this method generate the constraint in the SQL schema? – DaveB Aug 14 '12 at 17:24
1

Yes it does support. It is documented here: http://docs.oracle.com/javaee/6/api/javax/persistence/Table.html#uniqueConstraints%28%29 Something else must be wrong. Is javax.persistence.UniqueConstraint imported? – Mikko Maunu Aug 20 '12 at 10:56
23

I know this was years ago, but make sure you're using the `javax.persistence.Table` not `org.hibernate.annotations.Table`. Hibernate's `@Table` does not have a `uniqueConstraints` field. – Patrick Feb 05 '14 at 18:21

score 2 · Answer 2 · answered Sep 29 '20 at 16:37

2

In a more modern syntax, it would be :

@Table(
    name="ACTIVE_BAND", 
    uniqueConstraints =
        [UniqueConstraint(
                columnNames = ["active_band_user", "active_band_date"]
        )]
)

answered Sep 29 '20 at 16:37

NJellab

21
1

score 0 · Answer 3 · answered May 19 '22 at 21:17

This syntax worked for me with Java 17

@Entity
@Table(name = "ELECTION_RESULTS",
    uniqueConstraints = { @UniqueConstraint(name = "UC_CP", columnNames = { "constituency", "party_code" }) })
  
public class ElectionResults {
 @Column(..)
 ......
}

Unique Constraint Over Multiple Columns

3 Answers3

Linked