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

Question

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

• lin: I guess you have execute the script several times, your db may already have a record with pk 'ABCDE12345'.So check your db
• Oleg: What is your spring.datasource.url? Verify that you're using an in-memory database.
• Kartik: @Oleg Updated the post.
• Oleg: You're using a persistent database, how are you expecting it to work? After running it for the first time your Person table is already populated, trying to insert the same data again and getting primary key violation is expected. Try replacing it with jdbc:h2:mem:rest

Answer 1

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.

• Saurabh Gour: For my case, I had the schema.sql & data.sql files already present under src/main/resource and had also added the same files under src/test/resources which was causing the issue. So, basically for test the files under src/main/resources will get applied first.

Answer 2

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.