CODEWITHSUNDEEP
javahibernatejpah2unique-constraint
OferBr
OferBr

Reputation: 397

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

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:

Upvotes: 2

Views: 5362

Answers (3)

milad
milad

Reputation: 1

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.

Upvotes: 0

Ryan Bennetts
Ryan Bennetts

Reputation: 1183

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.

Upvotes: 0

Alan Hay
Alan Hay

Reputation: 23226

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?

Upvotes: 1

Related Questions