JPA's @UniqueConstraint annotation doesn't seem to guarantee uniqueness

Question

I have this code:

@Table(uniqueConstraints = @UniqueConstraint(columnNames = {"name", "color"}))
@MappedSuperclass
public abstract class AbstractInstance extends Model {

    @NotNull
    public String name;

    public String color;

    ...
}

but for some reason the @UniqueConstraint has no effect - I succeed in putting in the DB multiple instances with the exact same name and color (when I query for color='green' AND name='MyName' I get multiple results). Am I doing something wrong? Should I do something else for this compound uniqueness constraint to take effect?

Another evidence of the problem might be that when I query the DB's INFORMATION_SCHEMA.CONSTRAINTS regarding my relevant table, I get this result which doesn't seem to mention 'name' and 'color' as unique:

CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME    | CONSTRAINT_TYPE | UNIQUE_INDEX_NAME | CHECK_EXPRESSION | COLUMN_LIST  | SQL  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
play               | public            | fkcb0e606fe2f3066d | REFERENTIAL     | PRIMARY_KEY_8     | null         | CONTAINER_ID | ALTER TABLE PUBLIC.UNLABELEDINSTANCE ADD
                   |                   |                    |                 |                   |                  |              | CONSTRAINT PUBLIC.FKCB0E606FE2F3066D FOREIGN
                   |                   |                    |                 |                   |                  |              | KEY(CONTAINER_ID) INDEX
                   |                   |                    |                 |                   |                  |              | PUBLIC.FKCB0E606FE2F3066D_INDEX_C REFERENCES
                   |                   |                    |                 |                   |                  |              | PUBLIC.DATASET(ID) NOCHECK
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
play               | public            | constraint_d7      | PRIMARY KEY     | PRIMARY_KEY_C     | null             | ID           | ALTER TABLE PUBLIC.UNLABELEDINSTANCE ADD
                   |                   |                    |                 |                   |                  |              | CONSTRAINT PUBLIC.CONSTRAINT_D7 PRIMARY KEY(ID)
                   |                   |                    |                 |                   |                  |              | INDEX PUBLIC.PRIMARY_KEY_C

I am using:

Java 1.7
Hibernate via the maven dependency: org.hibernate.javax.persistence:hibernate-jpa-2.0-api:1.0.1.Final (I am not sure how to infer from this the version of JPA\Hibernate)
Play Framework 1.2.7
H2 database (the one that comes with Play Framework, for in-memory DB)

Did you use JPA to generate the database schema? Please show the schema dump. It's also possible that you have Hibernate configured in such a way that the column names aren't as you listed them in the annotation. — chrylis -cautiouslyoptimistic-, Oct 21 '14 at 22:50
How do I produce a schema dump? And what is the relevant Hibernate configuration? — OferBr, Oct 21 '14 at 22:53
I don't seem to have a persistence.xml in my project. I think the Play Framework handles all of those things internally. — OferBr, Oct 22 '14 at 00:00
If you drop your database, the PlayFramework will recreate it? — uaiHebert, Oct 22 '14 at 00:02

score 1 · Answer 1 · edited May 23 '17 at 12:22

The only effect of specifying @UniqueConstraint is that a unique constraint will be added to the database schema if you are using your JPA provider's schema generation functionality. If not then it has absolutely no effect.

The obvious corollary of this is that if you want to catch unique constraint violations before hitting the database you will need to add validation in your code.

You must have a persistence.xml somewhere.

https://www.playframework.com/documentation/2.1.0/JavaJPA

As noted above, you need to add the following property

Hibernate hbm2ddl.auto possible values and what they do?

score 0 · Answer 2 · edited May 23 '17 at 11:52

The class you have the @UniqueConstaint defined on is a @MappedSuperClass. But unique constraints aren't inherited by derived entities. You will either need to define the unique constraint on each derived entity or use an orm.xml file.

See this answer for more: How to use @UniqueConstraint with single table inheritance (JPA)?

Also, as noted in the other answers and comments to your question: because uniqueness is only enforced by the database, the @UniqueConstraint only comes into play if you are using JPA to generate the schema.

score 0 · Answer 3 · answered Oct 17 '20 at 11:00

0

if you created the table before using @UniqueConstraint annotation you can drop the table and create that with use @UniqueConstraint annotation. in this scenario, @UniqueConstraint annotation working fine.

answered Oct 17 '20 at 11:00

milad

1
3

JPA's @UniqueConstraint annotation doesn't seem to guarantee uniqueness

3 Answers3