Primary key violation issue oracle db-CodePudding

currently I am migrating one column data to new table and I created one sequence to generate my primary key like this

CREATE SEQUENCE seq_my_generated
  MINVALUE 1
  MAXVALUE 99999999
  START WITH 1
  INCREMENT BY 1

and I migrated data using below script

INSERT INTO my_new_table( new_table_pk, old_table_pk_as_fk, attachment) SELECT seq_my_generated.nextval, old_table_pk, attachment FROM old_table

till then everything worked fine, now in actual environment new data will be inserted to new table via my java application, my Java code for my new table looks like this

@Entity
@Table(name="my_new_table")
public class NewTable{

@Id
@GeneratedValue(strategy = SEQUENCE, generator ="seq_my_generated")
@SequenceGenerator(name="seq_my_generated")
@Column(name="new_table_pk")
long id;
//rest parameters
}

now when I try to insert new data via application I am getting Primary Key uniqueness Violation exception, My assumption is like it is due when I migrated data using db script, my java code is unaware of what's the last value used so it tries to reuse same key which is already exist in the table can anyone please tell me if my assumption is correct or what could be the issue?

CodePudding user response：

Well, you could verify your assumption by selecting next sequence value and checking whether it already exists in the table.

Alternatively - and possibly better - recreate the sequence. First, find the maximum primary key column's value:

select max(new_table_pk) from my_new_table;

Presume it returned 2254.

Then create the sequence as max PK column value 1:

SQL> create sequence seq_my_generated start with 2255;

Sequence created.

SQL> select seq_my_generated.nextval from dual;

   NEXTVAL
----------
      2255

SQL>

Therefore, when you next time run that code of yours, there won't be any duplicates any more.

CodePudding user response：

As per this article I had increment size of 1 in my db sequence, and by default allocation size is 50 in spring-jpa, that means at every 50 counts it queries db to get new id, since I migrated data from db script till my 50 count my spring-jpa won't go and query to the db, so I changed my allocationSize=1 in my sequence generator which solved my issue