Hibernate – ConstraintViolationException: duplicate key value violates unique constraint
1. Presentation of the context :
The model is as described below :
@Entity
@Table(name = "user")
public class User implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", nullable = false)
private Long id;
@Column(name = "name", nullable = false)
private String name;
@Column(name = "email", nullable = false)
private String email;
// getter and setter
…
}
The repository is:
@Repository
public interface UserRepository extends PagingAndSortingRepository<User, Long>, JpaSpecificationExecutor<User> {
Optional<User> findByEmail(String email);
}
The SQL query in data.sql used for inserting new users is:
INSERT INTO database_schema."user"(id, name, email) values
(1, 'User 1', email1@example.com') ,(2, 'User 2', 'email2@example.com');
2. The exception :
While inserting a new user via the repository, Hibernate throws “ConstraintViolationException: could not execute statement” when inserting a new object into the database.
User newUser = new User();
company.setName("User 3");
company.setEmail("email3@example.com");
// Save user
companyRepository.save(newUser);
The exception thrown by PostregreSQL is:
“org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint”
3. The root cause :
The insertion of a new user via SQL queries from data.sql does not increment the sequence number of the user table identifier.
select currval('database_schema.user_id_seq');
-- This query returns 1
4. The solution :
We have to manage the incrementation of each primary key sequence when we insert new data via data.sql
The new data.sql file after modification will be:
INSERT INTO database_schema."user"(id, name, email) values
(1, 'User 1', email1@example.com') ,(2, 'User 2', 'email2@example.com');
-- increment the user sequence
select setval('database_schema.user_id_seq', 2);
The next time the application inserts new data into the database, the new value that will be used for the id field of the user table will be provided by the nextval method, which will be 3.