CODEWITHSUNDEEP
oracle-databaseoracle11g
scedsh8919
scedsh8919

Reputation: 151

Oracle Insert & Auto-Increment Issue

We have an old program built upon an Oracle 11g database. When we create a new record, a new row is created for the record, with the primary key being an auto-increment number.

We recently had a large batch of records that needed inserting and was done through an insert statement. However, now when a user goes to create a new record, the program is not recognizing that there were records inserted into the table and is trying to begin at an incorrect position. This is violating the primary key constraint.

For example, the last record created in the program was 5001, but we inserted 98 records through an insert statement. When a user attempts to create a new record in the program, the program believes the record ID should be 5002, when 5002 already exists; it should create a new record with the ID 5100.

Is there a way to "refresh" this to make the program recognize the additional records?

Typically, a record is created when a user click on "New Record" from the program UI. A new record ID and row is created, additional information is added to the record, and the record is then saved.

The batch was simply an INSERT statement containing data for the new records, with the record ID beginning where the current record ID ended. This left no gaps in the sequence.

Upvotes: 1

Views: 813

Answers (1)

John A
John A

Reputation: 81

It sounds like the batch insert did not call the sequence your program is using.

You can drop the sequence and recreate it starting at 5100:

You can issue a DROP SEQUENCE command followed CREATE SEQUENCE starting at 5100.

DROP SEQUENCE sequence_name;

CREATE SEQUENCE sequence_name start with 5100;

Or you could modify the existing sequence with ALTER SEQUENCE.

If the next value for your current sequence is 5002, you could:

ALTER SEQUENCE sequence_name increment by 97;

SELECT sequence_name.nextval from dual;

   NEXTVAL
----------
      5099

ALTER SEQUENCE sequence_name increment by 1;

The next call would return 5100.

Upvotes: 3

Related Questions