Unique index or Primary key Violation

Question

I am getting the error below when trying to create a new item in a table, in this table I have some previously added items.

2019-03-17 00:05:09 WARN  o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 23505, SQLState: 23505
2019-03-17 00:05:09 ERROR o.h.e.jdbc.spi.SqlExceptionHelper - Unique index or primary key violation: "PUBLIC.PRIMARY_KEY_3 ON PUBLIC.LOJA_DOCUMENTO(ID_DOCUMENTO) VALUES 1"; SQL statement:
/* insert br.com.odin.loja.backend.model.Documento */ insert into loja_documento (codigo_documento, descricao_documento, id_pessoa, valor_documento, id_documento) values (?, ?, ?, ?, ?) [23505-198]
2019-03-17 00:05:09 ERROR o.h.i.ExceptionMapperStandardImpl - HHH000346: Error during managed flush [org.hibernate.exception.ConstraintViolationException: could not execute statement]
2019-03-17 00:05:09 ERROR o.a.c.c.C.[.[.[.[dispatcherServlet] - Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint ["PUBLIC.PRIMARY_KEY_3 ON PUBLIC.LOJA_DOCUMENTO(ID_DOCUMENTO) VALUES 1"; SQL statement:
/* insert br.com.odin.loja.backend.model.Documento */ insert into loja_documento (codigo_documento, descricao_documento, id_pessoa, valor_documento, id_documento) values (?, ?, ?, ?, ?) [23505-198]]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement] with root cause
org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Unique index or primary key violation: "PUBLIC.PRIMARY_KEY_3 ON PUBLIC.LOJA_DOCUMENTO(ID_DOCUMENTO) VALUES 1"; SQL statement:
/* insert br.com.odin.loja.backend.model.Documento */ insert into loja_documento (codigo_documento, descricao_documento, id_pessoa, valor_documento, id_documento) values (?, ?, ?, ?, ?) [23505-198]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:457) ~[h2-1.4.198.jar:1.4.198]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:427) ~[h2-1.4.198.jar:1.4.198]

This is my model:

package br.com.odin.loja.backend.model;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;

@Entity
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
@Table(name = "loja_documento")
public class Documento {

    // Attributes
    @Id
    @Column(name = "ID_DOCUMENTO", nullable = false, precision = 3, scale = 0)
    @GeneratedValue(generator = "seq_generator", strategy = GenerationType.SEQUENCE)
    @SequenceGenerator(name="seq_generator", sequenceName = "SEQ_LOJA_DOCUMENTO", allocationSize=1)
    private Integer id;

    @Column(name = "CODIGO_DOCUMENTO", nullable = false, length = 25)
    private String codigo;

    @Column(name = "DESCRICAO_DOCUMENTO", nullable = false, length = 25)
    private String descricao;

    @Column(name = "VALOR_DOCUMENTO", nullable = false, length = 25)
    private String valor;

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "ID_PESSOA")
    @JsonIgnoreProperties(value = "documentos", allowSetters = true)
    private Pessoa pessoa;

    // Constructors

    // Getters & Setters
    public Integer getId() {
        return id;
    }

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

    public String getCodigo() {
        return codigo;
    }

    public void setCodigo(String codigo) {
        this.codigo = codigo;
    }

    public String getDescricao() {
        return descricao;
    }

    public void setDescricao(String descricao) {
        this.descricao = descricao;
    }

    public String getValor() {
        return valor;
    }

    public void setValor(String valor) {
        this.valor = valor;
    }

    public Pessoa getPessoa() {
        return pessoa;
    }

    public void setPessoa(Pessoa pessoa) {
        this.pessoa = pessoa;
    }

    // Methods

}

As far as I can understand I am trying to add the id 1 in the table but this id already exists in the table. I don’t know how to make Hibernate understand that it is to get the next value of the list. I thank you already.

Sincerely yours truly, Eduardo

Answer 1

Since you already have previously persisted data in this table, identify the largest id currently added in this table and use the property initialValue from that value, for example:

Select max(id) from table

If the biggest id of your table for 4, you will use initialValue = 5:

@SequenceGenerator(name = "seq_generator", sequenceName = "SEQ_LOJA_DOCUMENTO", initialValue = 5, allocationSize = 1)

Test also move the annotation @SequenceGenerator up to the class declaration:

@Entity
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
@Table(name = "loja_documento")
@SequenceGenerator(name = "seq_generator", sequenceName = "SEQ_LOJA_DOCUMENTO", initialValue = 5, allocationSize = 1)
public class Documento {

Despite the recommendations, if you are interested you can replace your strategy for strategy=GenerationType.IDENTITY. This will make the spring managed the id for auto_increment bank. So you won’t have this problem of generating a id that already exists in the bank.

In case it would look like this:

@Id
@Column(name = "ID_DOCUMENTO", nullable = false, precision = 3, scale = 0)
@GeneratedValue(strategy=GenerationType.IDENTITY)
private Integer id;

I recommend that you test one of the recommendations at a time.

Reference Java University

Devmedia reference