CODEWITHSUNDEEP
triggersoracle10gsequenceauto-increment
user3239687
user3239687

Reputation: 29

On auto increment in Oracle10g for ID, if a row is deleted, will the new row start with a new sequence or continue from the deleted sequence

I have implemented auto increment on my table in oracle using sequence and trigger. The first two records entered had IDs in a consecutive order, but on the entry of the third record, there was a gap. The ID was 8 for it. On deletion of the record and entery of the new record, the ID became 9. How do I remove the gap and get the ID of the 3rd record as 3 and in consecutive order for next. If a row is deleted will the new row start with a new sequence or continue from the deleted sequence? How do I solve this problem? Please help

Upvotes: 0

Views: 142

Answers (1)

Alex Poole
Alex Poole

Reputation: 191305

Gaps in a sequence are never reused. You should expect gaps anyway, even without deletions, due to caching, rollbacks, RAC etc. You may not even get the sequences in the order you expect if you're using RAC. An ID, particularly an 'auto-incremented' one, is generally a synthetic primary key and the actual value of the key has no intrinsic meaning, it merely has to be unique.

Tom Kyte has a view on this subject, of course.

If gaps were re-used, what would happen if you added three records which got IDs 1, 2 and 3, and then deleted record 2? Would the next inserted record get ID 2 or 4? Even if you filled in the gap (which would mean serialising and basically ignoring the sequence) then the IDs are not in the order they were inserted, so you still can't discern anything from looking at the IDs. You don't know, from the ID, what order they were inserted.

If you really want seamless numbering for display purposes you can ignore the ID value and use a pseudo-column:

select t.column1, t.column2, ..., 
  row_number() over (order by t.id) as rn
from your_table t;

If you need to track the order they were inserted then add a timestamp field and set that to sys_timestamp in your trigger as well. You could then generate a seamless number in that order instead:

select t.column1, t.column2, ..., 
  row_number() over (order by t.inserted) as rn
from your_table t;

Upvotes: 2

Related Questions