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

Question

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]

Cavil

INSERT INTO SERMON_SESSION (id, session_enum) VALUES ('1', 'SUN_MRN') will insert the id, too. At least it tries to do so... Have a look at the column definition of id. It may be configured as UNIQUE and/or AUTO INCREMENT, which means the database will handle the id and you have to insert the values for the remaining columns only (INSERT INTO SERMON_SESSION (session_enum) VALUES ('SUN_MRN')); – Quipu

Hi @deHaar, many thanks for your reply. Please see update – Cavil

Hmm... This ('SUN_MRN', 1) is somehow suspicious because the order of the values is obviously reversed compared to your first try. Can you provide the definition of the table or the column id? – Quipu

Hi the definition of the table is above: SermonSession.java can be seen in the question and the enum is here: pastebin.com/E6eMuW7u. – Cavil

@Cavil any progress there? I'm trying to do the same thing and getting the same JdbcSQlException – Nansen

Answer 1

@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');

Gainly

I've tried this and now get the 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] – Cavil

@Cavil What does you enum look like? It smells fishy to me that the enum column should be unique too.. – Gainly

@Cavil try to truncate the table before inserting the values. I just edited my answer – Gainly

I have tried this and get the error: org.h2.jdbc.JdbcSQLException: Cannot truncate "PUBLIC.SERMON_SESSION"; SQL statement: TRUNCATE TABLE SERMON_SESSION [90106-197] – Cavil

@Cavil You probably have constraints on it. Try the following DELETE FROM SERMON_SESSION – Gainly

Answer 2

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.

Cincinnatus

I have tried this and get the error: org.h2.jdbc.JdbcSQLException: Cannot truncate "PUBLIC.SERMON_SESSION"; SQL statement: TRUNCATE TABLE SERMON_SESSION [90106-197] – Cavil

Answer 3

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

Constipate

Answer 4

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.

Deason

Answer 5

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

Angularity