CODEWITHSUNDEEP
javaspringh2
methuselah
methuselah

Reputation: 13216

Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.xxx"; SQL statement

I keep getting the following error message whenever my application boots up:

Caused by: org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.SERMON_SESSION(ID)"; SQL statement:
INSERT INTO SERMON_SESSION (id, session_enum) VALUES ('1', 'SUN_MRN'), ('2', 'SUN_EVE'), ('3', 'TUE_BIB'), ('4', 'FRI_BIB'), ('5', 'WKD_CNF') [23505-197]

How can I fix this? I have to assign the sermon session id myself as it is used in later columns.

application.properties

spring.h2.console.enabled=true
spring.h2.console.path=/h2
spring.datasource.url=jdbc:h2:mem:erc;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.datasource.initialization-mode=embedded
spring.jpa.properties.hibernate.hbm2ddl.import_files=classpath://resources/data.sql
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect
spring.jpa.show-sql=false
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.properties.hibernate.hbm2ddl.import_files_sql_extractor=org.hibernate.tool.hbm2ddl.MultipleLinesSqlCommandExtractor
logging.level.com.erc.api.*=DEBUG
logging.level.org.hibernate=OFF
logging.level.org.hibernate.SQL=INFO
logging.level.org.hibernate.type.descriptor.sql=INFO
logging.level.org.jaudiotagger=WARN
spring.servlet.multipart.max-file-size=60MB
spring.servlet.multipart.max-request-size=60MB
application.sermon_path=classpath://resources/files/sermons/}

SermonSession.java

@Table(name = "sermon_session", uniqueConstraints = @UniqueConstraint(columnNames = {"id", "sessionEnum"}))
@Entity
public class SermonSession {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(updatable = false, nullable = false, unique = true)
    private int id;
    @Enumerated(EnumType.STRING)
    @Column(unique = true)
    private SessionEnum sessionEnum;

    public SermonSession(String session) {
        setSessionEnum(session);
    }

    public SermonSession() {
        this.sessionEnum = null;
    }

    public String toString() {
        return String.format("{ id: %d, session_enum: %s }", getId(), getSessionEnum());
    }

    String getSessionEnum() {
        return this.sessionEnum.getSession();
    }

    private void setSessionEnum(String session) {
        this.sessionEnum = SessionEnum.fromSession(session);
    }

    private int getId() {
        return id;
    }

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

data.sql

INSERT INTO SERMON_SESSION (id, session_enum)
VALUES ('1', 'SUN_MRN'),
       ('2', 'SUN_EVE'),
       ('3', 'TUE_BIB'),
       ('4', 'FRI_BIB'),
       ('5', 'WKD_CNF');

Changing data.sql to:

INSERT INTO SERMON_SESSION (session_enum)
VALUES ('SUN_MRN'),
       ('SUN_EVE'),
       ('TUE_BIB'),
       ('FRI_BIB'),
       ('WKD_CNF');

Produces the following error message:

Caused by: org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: "UK_AA7KWY8HO9GLHF1VI4HDB61X8_INDEX_8 ON PUBLIC.SERMON_SESSION(SESSION_ENUM) VALUES ('SUN_MRN', 1)"; SQL statement:
INSERT INTO SERMON_SESSION (session_enum) VALUES ('SUN_MRN'), ('SUN_EVE'), ('TUE_BIB'), ('FRI_BIB'), ('WKD_CNF') [23505-197]

Upvotes: 13

Views: 53260

Answers (5)

badger
badger

Reputation: 3256

I had the same issue I solved it by changing my query like this in flyway:

INSERT INTO crawl_template (id, template, domain) VALUES (NEXTVAL('CRAWL_TEMPLATE_ID_SEQ'), 'woo', 'example.com');

so in summary it is solved by adding NEXTVAL

Upvotes: 1

Federico Luna
Federico Luna

Reputation: 1

Hi I can solved this issue by adding this annotation to the id propertie @GeneratedValue

Upvotes: 0

stoneshishang
stoneshishang

Reputation: 443

For me, I got the exactly same error message. It didn't affect my CRUD operation but still annoying to see. I followed this article to resolve this error.

TLDR

To resolve this issue pass primary key column value as unique and not NULL. just simply add NOT NULL UNIQUE at the end of your DDL.

Upvotes: 0

Przemysław Gęsieniec
Przemysław Gęsieniec

Reputation: 648

I had a similar issue recently. The problem might be, that when you are not closing your application gracefully(you are not posting a message to the endpoint for application to shutdown), session manager might not have the opportunity to perform drop (see this topic: Spring boot ddl auto generator).

The solution we have found out is to manually drop the database and change 

spring.jpa.hibernate.ddl-auto=create

to 

spring.jpa.hibernate.hbm2ddl.auto=create

Why exactly is that? I'm still waiting for the answer here.

Upvotes: 3

Yassin Hajaj
Yassin Hajaj

Reputation: 21995

@GeneratedValue(strategy = GenerationType.IDENTITY) will allow an automatic unique ID generation, no need to put it there yourself.

The following should be more than enough

DELETE FROM SERMON_SESSION;
INSERT INTO SERMON_SESSION (session_enum)
VALUES ('SUN_MRN'),
       ('SUN_EVE'),
       ('TUE_BIB'),
       ('FRI_BIB'),
       ('WKD_CNF');

Upvotes: 13

Related Questions