CODEWITHSUNDEEP
javaspring-booth2
Kartik
Kartik

Reputation: 2609

Primary Key violation exception when trying to insert rows into H2 db in Spring boot application

I am trying to create a Spring Boot application that will perform CRUD operation. But when I start the app, it terminates with an exception. I don't know what I am doing wrong. Here is my DDL and DML. I don't know

schema.sql

CREATE TABLE IF NOT EXISTS Person(
    first_name VARCHAR(20),
    last_name VARCHAR(20),
    person_id varchar(10),
    PRIMARY KEY(person_id));
CREATE INDEX IF NOT EXISTS person_id_index on Person(person_id);
CREATE INDEX IF NOT EXISTS person_last_name_index on Person(last_name);

data.sql

INSERT INTO Person(person_id,first_name,last_name) VALUES('ABCDE12345','Jane','Doe');

Error log

018-02-03 23:12:33.916  INFO 88786 --- [           main] com.springboot.rest.PersonDaoTest       : Started PersonDaoTest in 336.053 seconds (JVM running for 336.71)
2018-02-03 23:12:34.023  INFO 88786 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executing SQL script from class path resource [schema.sql]
2018-02-03 23:12:34.023  INFO 88786 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executed SQL script from class path resource [schema.sql] in 0 ms.
2018-02-03 23:12:34.023  INFO 88786 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executing SQL script from class path resource [data.sql]
2018-02-03 23:12:34.037  WARN 88786 --- [           main] o.s.test.context.TestContextManager      : Caught exception while allowing TestExecutionListener [org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener@5c33f1a9] to process 'before' execution of test method [public void com.springboot.rest.PersonDaoTest.testCreatePerson() throws java.lang.Exception] for test instance [com.springboot.rest.PersonDaoTest@577f9dfd]

org.springframework.jdbc.datasource.init.ScriptStatementFailedException:
 Failed to execute SQL script statement #1 of class path resource [data.sql]: 
INSERT INTO Person(person_id,first_name,last_name) VALUES('ABCDE12345','Jane','Doe'); 
nested exception is org.h2.jdbc.JdbcSQLException:
 Unique index or primary key violation: "PRIMARY_KEY_8 ON 
   PUBLIC.PERSON(PERSON_ID) VALUES ('ABCDE12345', 1)"; SQL statement:
INSERT INTO Person(person_id,first_name,last_name) VALUES('ABCDE12345','Jane','Doe') [23505-196]
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:491) ~[spring-jdbc-4.3.12.RELEASE.jar:4.3.12.RELEASE]
    at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:238) ~[spring-jdbc-4.3.12.RELEASE.jar:4.3.12.RELEASE]
    at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:48) ~[spring-jdbc-4.3.12.RELEASE.jar:4.3.12.RELEASE]
. . . .
. . . . 
. . . . 
. . . .
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:207) [.cp/:na]
Caused by: org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: "PRIMARY_KEY_8 ON PUBLIC.PERSON(PERSON_ID) VALUES ('ABCDE12345', 1)"; SQL statement:
INSERT INTO Person(person_id,first_name,last_name) VALUES('ABCDE12345','Jane','Doe') [23505-196]

application.properties

server.contextPath=/rest
spring.h2.console.enabled=true
spring.h2.console.path=/rest/h2

spring.datasource.url=jdbc:h2:file:./rest
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql=true

Upvotes: 2

Views: 6874

Answers (2)

damndemon
damndemon

Reputation: 346

As Oleg pointed out in comments, H2 is in memory database causing data to be already present leading to pk constraint exception.

A simple task you can perform is to add DROP IF EXISTS statement in schema.sql so that any data existing is removed on every restart. But make sure you use it for testing purpose only and remove it before moving to production.

Upvotes: 0

Aleksei Budiak
Aleksei Budiak

Reputation: 921

I suppose your application at startup tries to add a record to the database with person_id = ABCDE12345 and you're getting the exception because such record already exists.

PRIMARY KEY(person_id) in your schema adds a constraint that makes sure only unique values are present in person_id column.

With this constraint you'll be getting Unique index or primary key violation error each time you add a record to the database with non-unique record_id.

Upvotes: 2

Related Questions