Spring Boot+H2数据库插入触发唯一索引/主键约束冲突问题求助

云原生阿岚

2026-8-25

解决Spring Boot+H2数据库插入时的唯一约束冲突问题

问题场景

在Spring Boot应用中使用H2数据库，通过自动建表功能创建表后，向所有实体类插入数据时均触发Unique index or primary key violation错误，以下是OrgEmployee实体的相关报错日志、代码及数据库配置：

报错日志

2022-07-21 07:23:58.113 ERROR 3459586 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : Unique index or primary key violation: "public.username_uk1_INDEX_3 ON public.org_employee(username NULLS FIRST) VALUES ( /* 9 */ 'test' )"; SQL statement:
insert into org_employee (created_by, created_time, lock_id, modified_by, modified_time, org_code, tags, label_ids, alternate_mobile_number, application_id, auth_token, default_process_code, email, external_ref, external_ref2, failed_attempt_count, force_change_passwd, kyc_approved, kyc_id, landline_with_std, last_failed_attempt_time, last_login_time, locale, mobile_number, name, one_time_password, one_time_password_expiry, password_hash, previous_passwords, pwd_change_time, roles, self_signed_user, status, username, username_uc, whatsapp_number) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [23505-212]
2022-07-21 07:23:58.114 ERROR 3459586 --- [           main] c.e.e.service.impl.EntityServiceImpl     : Exception in saveOrUpdate 

org.hibernate.exception.ConstraintViolationException: could not execute statement
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:72)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:211)
    at org.hibernate.id.insert.AbstractSelectingDelegate.performInsert(AbstractSelectingDelegate.java:61)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3032)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3558)
    at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:98)
    at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:492)
    at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:197)
    at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:181)
    at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:216)
    at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:334)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:289)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:195)
    at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:126)
    at org.hibernate.jpa.event.internal.core.JpaSaveOrUpdateEventListener.saveWithGeneratedId(JpaSaveOrUpdateEventListener.java:73)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:209)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:194)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:114)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:90)
    at org.hibernate.internal.SessionImpl.fireSaveOrUpdate(SessionImpl.java:684)
    at org.hibernate.internal.SessionImpl.saveOrUpdate(SessionImpl.java:676)
    at org.hibernate.internal.SessionImpl.saveOrUpdate(SessionImpl.java:671)
    at com.ezetap.entity.service.impl.EntityServiceImpl.saveOrUpdateInTransaction(EntityServiceImpl.java:82)
    at com.ezetap.entity.service.impl.EntityServiceImpl$$FastClassBySpringCGLIB$$5516f1da.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:720)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655)
    at com.ezetap.entity.service.impl.OrgEmployeeServiceImpl$$EnhancerBySpringCGLIB$$b5843870.saveOrUpdateInTransaction(<generated>)
    at com.ezetap.entity.service.impl.EntityServiceImpl.saveOrUpdate(EntityServiceImpl.java:57)
    at com.ezetap.entity.service.impl.OrgEmployeeServiceImpl.saveOrUpdate(OrgEmployeeServiceImpl.java:55)
    at com.ezetap.entity.service.impl.OrgEmployeeServiceImpl.saveOrUpdate(OrgEmployeeServiceImpl.java:32)
    at com.ezetap.entity.service.impl.OrgEmployeeServiceImpl$$FastClassBySpringCGLIB$$819071b.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:651)
    at com.ezetap.entity.service.impl.OrgEmployeeServiceImpl$$EnhancerBySpringCGLIB$$b5843870.saveOrUpdate(<generated>)
    at com.ezetap.tools.seed.loader.demo.TestOrgLoader.load(TestOrgLoader.java:74)
    at com.ezetap.tools.seed.SeedMain.load(SeedMain.java:102)
    at com.ezetap.tools.seed.SeedMain$$FastClassBySpringCGLIB$$a0909166.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:720)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655)
    at com.ezetap.tools.seed.SeedMain$$EnhancerBySpringCGLIB$$2a9a840.load(<generated>)
    at com.ezetap.tools.seed.SeedMain.run(SeedMain.java:90)
    at com.ezetap.tools.seed.SeedMain$$FastClassBySpringCGLIB$$a0909166.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:651)
    at com.ezetap.tools.seed.SeedMain$$EnhancerBySpringCGLIB$$2a9a840.run(<generated>)
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:806)
    at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:790)
    at org.springframework.boot.SpringApplication.afterRefresh(SpringApplication.java:777)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:308)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1191)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1180)
    at com.ezetap.tools.seed.SeedMain.main(SeedMain.java:38)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:54)
    at java.lang.Thread.run(Thread.java:748)Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Unique index or primary key violation: "public.username_uk1_INDEX_3 ON public.org_employee(username NULLS FIRST) VALUES ( /* 9 */ 'test' )"; SQL statement:
insert into org_employee (created_by, created_time, lock_id, modified_by, modified_time, org_code, tags, label_ids, alternate_mobile_number, application_id, auth_token, default_process_code, email, external_ref, external_ref2, failed_attempt_count, force_change_passwd, kyc_approved, kyc_id, landline_with_std, last_failed_attempt_time, last_login_time, locale, mobile_number, name, one_time_password, one_time_password_expiry, password_hash, previous_passwords, pwd_change_time, roles, self_signed_user, status, username, username_uc, whatsapp_number) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [23505-212]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:508)
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:477)
    at org.h2.message.DbException.get(DbException.java:223)
    at org.h2.message.DbException.get(DbException.java:199)
    at org.h2.index.Index.getDuplicateKeyException(Index.java:525)

OrgEmployee实体类代码

@Entity
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
@Cacheable(true)
@Table(name = "org_employee", uniqueConstraints = @UniqueConstraint(name = "username_uk1", columnNames = {
    "username"}), indexes = {
    @Index(name = "idx_org_code_username", columnList = "org_code,username"),
    @Index(name = "idx_org_code", columnList = "org_code"),
    @Index(name = "idx_username_uc", columnList = "username_uc"),
    @Index(name = "idx_mobile_number", columnList = "mobile_number"),
    @Index(name = "idx_whatsapp_number", columnList = "whatsapp_number")
})
public class OrgEmployee extends LabelledEntity<Long> implements IAuthEntity {

    private static final long serialVersionUID = 1L;

    private static final String ROLE_SEPARATOR = ",";
    private static final String HASH_SEPARATOR = "\\|\\|";
    private static final int MAX_PREV_PASSWORDS = 6;

    @GeneratedValue(strategy = GenerationType.AUTO)
    @Id
    private Long id;
    @Column(name = "username", length = 20, nullable = false)
    private String username;
    @Column(name = "username_uc", length = 20, nullable = false)
    private String usernameUc;
    @Column(name = "password_hash", length = 50)
    private String passwordHash;
    @Column(name = "mobile_number", length = 13)
    private String mobileNumber;
    @Column(name = "whatsapp_number", length = 13)
    private String whatsappNumber;
    @Column(name = "email", length = 50)
    private String email;
    @Column(name = "name", length = 100)
    private String name;
    @Column(length = 10)
    @Enumerated(EnumType.STRING)
    private ActivityStatus status = ActivityStatus.ACTIVE;
    @Column(name = "default_process_code", length = 25)
    private String defaultProcessCode;
    @Column(name = "roles", length = 256)
    private String roles;
    @Column(name = "pwd_change_time")
    private Date pwdChangeTime = new Date();
    @Column(name = "failed_attempt_count")
    private int failedAttemptCount = 0;
    @Column(name = "last_failed_attempt_time")
    private Date lastFailedAttemptTime;
    @Column(name = "force_change_passwd")
    private boolean forceChangePasswd = false;
    @Column(name = "one_time_password", length = 10)
    private String oneTimePassword;
    @Column(name = "one_time_password_expiry")
    private Date oneTimePasswordExpiry;
    @Column(name = "previous_passwords", length = 310)
    private String previousPasswordHashes;
    @Column(name = "application_id", length = 64)
    private String applicationId;
    //new fields from annexure H
    @Column(name = "alternate_mobile_number", length = 13)
    private String alternateMobileNumber;
    @Column(name = "landline_with_std", length = 16)
    private String landlineNumber;

    //locale field
    @Column(name = "locale", length = 16)
    @Type(type = "org.hibernate.type.LocaleType")
    private Locale locale = Locale.ENGLISH;
    @Column(name = "auth_token", length = 32)
    private String authToken;
    @Column(name = "external_ref", length = 64)
    private String externalRef;
    @Column(name = "external_ref2", length = 64)
    private String externalRef2;
    @Column(name = "self_signed_user")
    private boolean selfSignedUser;
    @Column(name = "kyc_approved")
    private boolean kycApproved;
    @Column(name = "kyc_id", length = 64)
    private String kycId;
    @Column(name = "last_login_time")
    private Date lastLoginTime;
  

/// getter and setter

}

H2数据库配置

database.driver=org.h2.Driver
database.url=jdbc:h2:file:~/home/data/ezetap-h2;MODE=MySQL;DATABASE_TO_LOWER=TRUE;AUTO_SERVER=TRUE
database.user=root
database.password=root123 
spring.h2.console.enabled=true
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

排查与解决步骤

定位冲突根源
报错明确指向username_uk1唯一约束，冲突值为test，说明H2文件数据库中已存在username='test'的记录。由于使用的是持久化文件型H2数据库，之前启动应用插入的数据会保留在本地文件中，重复插入相同username的记录就会触发约束冲突。
快速清除旧数据（测试环境）
1. 停止Spring Boot应用；
2. 找到H2数据库文件~/home/data/ezetap-h2.mv.db（H2文件型数据库的核心存储文件），直接删除；
3. 重启应用，Spring Boot会自动重建空表，此时插入新数据即可避免冲突。

优化插入逻辑（生产/需保留数据场景）
在执行插入前先检查数据是否存在，避免重复插入：

// 在OrgEmployeeServiceImpl中实现存在则更新、不存在则插入的逻辑
public OrgEmployee saveOrUpdate(OrgEmployee employee) {
    Optional<OrgEmployee> existingEmp = orgEmployeeRepository.findByUsername(employee.getUsername());
    if (existingEmp.isPresent()) {
        // 合并需要更新的字段
        OrgEmployee updateEmp = existingEmp.get();
        updateEmp.setName(employee.getName());
        updateEmp.setMobileNumber(employee.getMobileNumber());
        updateEmp.setEmail(employee.getEmail());
        // 其他需要更新的字段
        return orgEmployeeRepository.save(updateEmp);
    } else {
        // 执行插入操作
        return orgEmployeeRepository.save(employee);
    }
}

注意：需要在OrgEmployeeRepository中添加findByUsername方法：

public interface OrgEmployeeRepository extends JpaRepository<OrgEmployee, Long> {
    Optional<OrgEmployee>

Spring Boot+H2数据库插入触发唯一索引/主键约束冲突问题求助

问题场景 #

报错日志 #

OrgEmployee实体类代码 #

H2数据库配置 #

排查与解决步骤 #

问题场景

报错日志

OrgEmployee实体类代码

H2数据库配置

排查与解决步骤