CODEWITHSUNDEEP
postgresqlhibernatejpaconstraints
VHanded
VHanded

Reputation: 2089

Hibernate: duplicate key value violates unique constraint

I am trying to insert into a new record to my postgresql using Hibernate and Java EE.

This is my model:

@Entity
@SuppressWarnings("serial")
@Inheritance(strategy=InheritanceType.JOINED)
public class BaseDesign implements Serializable {

    @Id
    @Expose
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", updatable = false, nullable = false)
    private Long id;

    @Version
    @Column(name = "version")
    private int version;

    @Expose
    @Column(name = "name")
    private String name;

    // getter and setter
}

And here is my import.sql

INSERT INTO basedesign (id, name, version) VALUES (1, 'China', 0);

When I build the code, the record is added into db.

However, when I try to add a new record using EntityManager, like this:

BaseDesign design = new BaseDesign();
design.setName("USA");                  
em.persist(design);

I got: 

Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "basedesign_pkey"
  Detail: Key (id)=(1) already exists.

Execute the same command for second time, and it success.

Why do Hibernate not increment the starting ID at the first time? And how to configure it to start at the last inserted integer?

Upvotes: 15

Views: 24535

Answers (4)

JiBz
JiBz

Reputation: 31

Please see my answer here How to avoid id conflicts (uniqueconstraint violation) when using PanacheEntity together with Quarkus "import.sql"

In a nutshell you can do the following:

INSERT INTO ticket(id, name,seat) VALUES (nextval('hibernate_sequence'), 'Phantom of the Opera','11A')

Upvotes: 2

NikolaS
NikolaS

Reputation: 523

As @Pipo and @Adrian Cox suggested sequence needs to be manually edited so there are no collisions with primary keys.

I used alter sequence tableName_table_id_seq restart with 2; command AT THE END of SQL script to avoid this error you posted for getting

duplicate key value violates unique constraint

For instance, I used it like this (for my project) in SQL script for filling values when starting my application:

INSERT INTO author(author_id, first_name, last_name) VALUES(9, 'Dan', 'Brown');
INSERT INTO book(book_id, amount, is_deleted, title, price) VALUES(17, 140, false, 'Hamlet', 22.61);
-- other inserts
-- ...
alter sequence author_author_id_seq restart with 10;
alter sequence book_book_id_seq restart with 18;

Upvotes: 1

Pipo
Pipo

Reputation: 5073

With pgAdmin4 I you can change the incrementor to start with 2 (or more) as followings enter image description here

Upvotes: 3

Adrian Cox
Adrian Cox

Reputation: 6334

When you create a bigserial column in Postgresql, you are actually creating a sequence. When you manually inserted an ID value of '1', Postgresql did not update the sequence to take this into account. Hibernate lets Postgresql use the sequence to generate the ID, but the first value produced is '1', which clashes. The second value is fine.

If you created the problem by going behind Hibernate and using SQL directly, you should fix it the same way: use ALTER SEQUENCE to set the next value:

alter sequence basedesign_id_seq restart with 2;

Upvotes: 22

Related Questions