CODEWITHSUNDEEP
javahibernatepostgresqlnatural-key
Marcel
Marcel

Reputation: 4402

Why doesn't @NaturalId create a unique constraint in the database?

I'm having following Entity-Snipped

@Entity
public class EntityWithNaturalId extends BaseEntity {
    @NaturalId public String name;

    private EntityWithNaturalId() {}

    public EntityWithNaturalId(String name) {  this.name = name;  }
}

If I try to persist an entity with the same name twice I get following error (as expected):

org.hibernate.exception.ConstraintViolationException: could not execute statement
Eindeutiger Index oder Primärschlüssel verletzt:
     "_UNIQUEKEY_INDEX_6 ON PUBLIC.ENTITYWITHNATURALID(NAME) VALUES ('AA', 1)"
Unique index or primary key violation:
     "_UNIQUEKEY_INDEX_6 ON PUBLIC.ENTITYWITHNATURALID(NAME) VALUES ('AA', 1)"

What suprises me is that if I have a look at the table constraints in the database, I don't see anything. Shouldn't there be a unique constraint?

Postgres 9.3.2 Hibernate 4.2.0

EDIT: this post before is missleading due to the fact that I reduced facts to simplify the situation. By doing this I made this post inconsistent!!

@Vlad was right, but I realized that the problem lies somewhere else.

What I missed to write is that there are several other entities which use @NaturalId. Hibernate tries to create all constrains with name _UNIQUEKEY. (Prefix is missing)

Only first one is really created. All other ones throw this exception:

 2014-11-10 13:18:10 ERROR org.hibernate.tool.hbm2ddl.SchemaExport     - HHH000389: Unsuccessful: alter table AnotherEntityWithNaturalId add constraint _UniqueKey unique (pid)
 2014-11-10 13:18:10 ERROR org.hibernate.tool.hbm2ddl.SchemaExport     - ERROR: relation "_uniquekey" already exists

It seems to me that the unique key names are not build correctly, probably a bug?

Upvotes: 2

Views: 1223

Answers (2)

softwarevamp
softwarevamp

Reputation: 857

Either

  1. use InnoDB for engine,
  2. or use setting: 
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect
  1. or combine with
    @Column(length = ...)

works!

Upvotes: 0

Vlad Mihalcea
Vlad Mihalcea

Reputation: 154200

The index is there:

 ON PUBLIC.ENTITYWITHNATURALID(NAME)

The exception clearly specifies the constraint violation exception.

In PgAdmin you should see it under Schemas - public - Tables - ENTITYWITHNATURALID - Constraints - _UNIQUEKEY_INDEX_6

If you don't see it try to run this SQL command:

ALTER TABLE ENTITYWITHNATURALID ADD CONSTRAINT _UNIQUEKEY_INDEX_6 UNIQUE (name);

You should get:

ERROR: relation "_UNIQUEKEY_INDEX_6" already exists
SQL state: 42P07

in case the constraint already exists.

Upvotes: 0

Related Questions