CODEWITHSUNDEEP
sqloracle-database
arsenal
arsenal

Reputation: 24164

Autoincrement in Oracle(Adjust the ID sequence after deleting)

CREATE TABLE test
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));

CREATE SEQUENCE test1_sequence
START WITH 1
INCREMENT BY 1;

INSERT INTO test (id, name) VALUES (test1_sequence.nextval,'Jon');
INSERT INTO test (id, name) VALUES (test1_sequence.nextval,'Hello');
INSERT INTO test (id, name) VALUES (test1_sequence.nextval,'Matt');
INSERT INTO test (id, name) VALUES (test1_sequence.nextval,'Bork');

And suppose if I deleted one record from this table by-

delete from test where id='2';

then If I do select query-

select * from test;

then I get 

  ID  Name    
  1    Jon    
  3    Matt
  4    Bork

So If I need to maintain the order of id, like as soon as I delete any data it adjusts the id automatically. So I should be getting the table as

ID  Name    
1    Jon    
2    Matt
3    Bork

Any suggestions how can I do this..

Upvotes: 2

Views: 2330

Answers (1)

phil
phil

Reputation: 707

If I remember correctly from articles on Ask Tom website, sequence DOES NOT guarantee that it will produce gap free numbers. So the way you are filling up your table and generating ID's will never be 100% gap free (even without deleting rows).

Here's one of the articles on that subject: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4343369880986

Also keep in mind that you are updating (what seems a) primary key column.

My suggestion is not to do it. If you need a ordered list of rows, use ROW_NUMBER and leave PK to be a surrogate key without attaching any "application meaning" to it.

Upvotes: 5

Related Questions