CODEWITHSUNDEEP
oracle-databaseoracle-apex
ata
ata

Reputation: 41

Impact of altering Oracle Sequence on existing data, specifically 'START WITH' value

I am in a situation in which I am working with a Oracle database. One of the tables in our database was incomplete was another branch was tracking their data in an Excel spreadsheet. After reformatting their data I was able to successfully append the data to the Oracle table. One of the steps I had to take in order to accomplish this was to manually assign new DISPOSAL_ID (the primary key for this particular table). My approach to this was to simply take the maximum value for DISPOSAL_ID for that table and increment by one for each new record I wished to append.

The problem now is that an end user is reporting that which she tries to enter a new record into that table she receives an error message from the Oracle APEX application: ORA - 00001: Unique constraint (table_name.PK) violated.

I suspect this has to do with the fact that the Oracle sequence generating primary keys for this table is yielding values that I had manually assigned when I appended the branch data to the table.

What should I do to update to update the sequence? The current sequence definition is: CREATE SEQUENCE "MP_DISPOSAL_EVENTS_SEQ1" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 861 CACHE 20 NOORDER NOCYCLE NOPARTITION /

If I replace the 'START WITH' value with the new maximum value for the DISPOSAL_ID will this resolve the issue? Will it have any impacts on the existing data? I apologize for the novice nature of this question but I am relatively new to this and have no one technical to support me in this venture. I thank you all in advance.

Upvotes: 0

Views: 647

Answers (2)

Beefstu
Beefstu

Reputation: 857

I never make a habbit of using a sequence number as a primary KEY for the very reason you mentioned.

I prefer using an IDENTITY column associated with each table so you can uniquely identify a single row. In addition, that's one less object in your table.

create table t1(
      seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
….
….

Create a temporary table with the IDENTITY column. Load it with a SELECT and rename the table. You will never have the problem again.

Going forward for new tables you may want to incorporate this feature. Good luck

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231721

You can't do an alter sequence to change the starting value. You can drop and re-create the sequence with a new starting value. The primary downside to that is that if there are any grants on the sequence, those grants will be lost and would need to be re-granted.

It would generally be better to change the increment, get the nextval of the sequence, and then set the increment back to 1. So, for example, if you added 150 new rows to the table

alter sequence MP_DISPOSAL_EVENTS_SEQ1 increment by 150;
select MP_DISPOSAL_EVENTS_SEQ1.nextval from dual
alter sequence MP_DISPOSAL_EVENTS_SEQ1 increment by 1;

Upvotes: 2

Related Questions