background:

background service testing found an error unique constraint on inserting data into the oracle database table, as follows:

### Error updating database.  Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (TEST53.SYS_C0032604) violated
### The error may exist in class path resource [sqlmapper/ACMClaimMapper.xml]
### The error may involve com.las.core.dal.dao.ACMClaimMapper.insert-Inline
### The error occurred while setting parameters
### SQL: insert into TBL_ACM_CLAIM ( SEQ_NO, ACCT_NO, FUND_CHANNEL,STATUS,CREATE_DATE,CSM_NAME,CSM_CARD,LOAN_DATE,LOAN_AMT ) values ( ?,?,?,?,?,?,?,?,?)
### Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (TEST53.SYS_C0032604) violated
; SQL []; ORA-00001: unique constraint (TEST53.SYS_C0032604) violated
; nested exception is java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (TEST53.SYS_C0032604) violated

cause:

according to the tip of the index number, found in the table field “SEQ_NO”, found that is because the test database is by another database synchronization, automatic sequence number in the table were disrupted, lead to the next when inserting data, automatically generates SQL serial number in the table has data, so the error led to the only constraints. Of course, data deletion errors and human error operations can cause this problem.

resolved:

first, we can query where the sequence number of this table is located (my table is TBL_ACM_CLAIM) :

select SEQ_ACM_CLAIM.NEXTVAL  from DUAL

do not execute this SQL, because if it is not executed once, the index will increase by +1. If the value of SEQ_NO in the table is greater than the value of the query, the index value will be updated. Set the sequence step size to 100 (usually 1). Execute the following SQL:

alter sequence SEQ_ACM_CLAIM increment by 100

note: SEQ_ACM_CLAIM is not a table name, and the index value cannot modify the current value, but can only be modified to increase, not decrease.

hint:

query all indexes:

select * from user_sequences;

delete index:

drop sequence SEQ_ACM_CLAIM;

Create indexes:

CREATE UNIQUE INDEX SEQ_ACM_CLAIM ON TBL_ACM_CLAIM(SEQ_NO);

summary:

delete library to run road, one second is enough.