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]

`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')`); — deHaar, May 10 '19 at 06:26
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`? — deHaar, May 10 '19 at 06:35
Hi the definition of the table is above: `SermonSession.java` can be seen in the question and the enum is here: https://pastebin.com/E6eMuW7u. — methuselah, May 10 '19 at 06:42
@methuselah any progress there? I'm trying to do the same thing and getting the same `JdbcSQlException` — KareemJ, Dec 29 '20 at 16:14

Yassin Hajaj · Accepted Answer · 2019-05-12T00:56:20.850

10

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

edited May 12 '19 at 00:56

answered May 10 '19 at 06:28

Yassin Hajaj

21,337
9
51
89

2

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]` – methuselah May 10 '19 at 06:28
@methuselah What does you `enum` look like? It smells fishy to me that the enum column should be unique too.. – Yassin Hajaj May 10 '19 at 06:30
@methuselah try to truncate the table before inserting the values. I just edited my answer – Yassin Hajaj May 10 '19 at 06:58
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]` – methuselah May 10 '19 at 19:54
@methuselah You probably have constraints on it. Try the following `DELETE FROM SERMON_SESSION` – Yassin Hajaj May 12 '19 at 00:56

score 3 · Answer 2 · edited Nov 28 '19 at 20:04

3

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.

edited Nov 28 '19 at 20:04

marc_s

732,580
175
1,330
1,459

answered May 10 '19 at 06:52

Przemysław Gęsieniec

618
6
15

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]` – methuselah May 10 '19 at 19:54

score 1 · Answer 3 · answered Sep 28 '21 at 19:43

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.

score 0 · Answer 4 · answered Mar 15 '23 at 20:18

0

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

answered Mar 15 '23 at 20:18

Federico Luna

1

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

4 Answers4

Linked