Can an INITIALLY DEFERRED constraint be defined using a Hibernate annotation?

Question

I have a table with a column that has a UNIQUE constraint. I want the constraint checking to be deferred to commit time.

If I create it using Postgres SQL like this (many columns omitted):

CREATE TABLE instrument
(
  id bigint NOT NULL,
  name character varying(255) NOT NULL,
  CONSTRAINT instrument_pkey PRIMARY KEY (id),
  CONSTRAINT instrument_name_key UNIQUE (name)
     DEFERRABLE INITIALLY DEFERRED
)

Then everything works as expected.

If I define it to Hibernate like this:

import java.io.Serializable;
import javax.persistence.*;
import org.hibernate.annotations.ForeignKey;

@Entity
@Table(name="instrument")
public class Instrument implements Versionable, Serializable {
    private static final long serialVersionUID = 1L;

    public static final String NAME_PROPERTY = "name";

    @Version
    private int version;

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQ_GEN")
    private Long id;

    @Column(name="name", unique=true, nullable=false)
    private String name;

    public Instrument() {
       // null constructor to make Hibernate happy
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    @Override
    public int hashCode() {
        return Objects.hashCode(getName());
    }

    @Override
    public boolean equals(Object obj) {
        if (obj instanceof Instrument) {
            Instrument other = (Instrument)obj;
            return Objects.equal(getName(), other.getName());
        }
        return false;
    }

    @Override
    public String toString() {
        return "Instrument [id=" + id + ", name=" + name + "]";
    }
}

And use hibernate.hbm2ddl create to create the table, the INITIALLY DEFERRED option is not specified for the name's UNIQUE constraint (as expected because I don't know how to ask for it.)

When I run the app bad things happen.

In particular the user is allowed to swap names between two instruments. If he tries to swap the names between inst1 and inst2 it throws an exception:

org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "instrument_name_key"
  Detail: Key (name)=(inst1) already exists.

So the question is: Is there an Hibernate annotation that can be used to specify the INITIALLY DEFERRED property of a constraint on a column?

PS: I'm not looking for a work-around. I have an extra step in the install/setup process that applies the constraint. What I'm hoping for is a way to eliminate that extra step.

Magnien

Do you remember if you ended up with a solution that worked for you? Ended up with the same problem and I'm a bit baffled that this can't be done properly in Hibernate. – Employment

Answer 1

Unfortunately, Hibernate does not have support for deferred constraints. https://hibernate.atlassian.net/browse/HHH-2248

You could try playing with entityManager.flush() method, let's say you have Instruments with names inst1 and inst2:

Instrument inst1 = entityManager.find(Instrument.class, 1);
// change name of first Instrument to some random one
inst1.setName("inst3");
entityManager.flush();
Instrument inst2 = entityManager.find(Instrument.class, 2);
inst2.setName("inst1");
entityManager.flush();
inst1.setName("inst2");

Alternatively you could get the entities from DB, delete them from DB, perform flush and persist updated entities. This way you don't have to make up the third name.

Not sure about performance effect of those solutions, you have to figure out yourself.

Senseless

Answer 2

If you're using Flyway or other db versioning/migration tool, you could simply add another version DDL file after the one that defines the constraints as generated by Hibernate/JPA. This additional file would redefine those constraints to be DEFERRABLE INITIALLY DEFERRED.

Countercheck

Yes but this solution doesn't work if you rely on another database (e.g. H2) for testing. – Employment