Search code examples
oracleidentity-column

How to move up ID in autoincrement column Oracle SQL Developer


Now the highest ID in ID column is 192409. I need Oracle to start adding new values ​​from number 192734 (I want to values between 192409 and 192734 will be empty). How can I do this?

This is how this column is configured

("ID" NUMBER(38,0) GENERATED BY DEFAULT ON NULL AS IDENTITY 
MINVALUE 1 MAXVALUE 9999999999999999999999999999 
INCREMENT BY 1 START WITH 125 CACHE 20 NOORDER  NOCYCLE  NOT NULL ENABLE)

I tried to do update on last ID, but it didnt work.


Solution

  • Alter table, apparently.

    Sample table and data:

    SQL> create table test
      2    (id   number generated by default on null as identity,
      3     name varchar2(20));
    
    Table created.
    
    SQL> insert into test (name) values ('Little');
    
    1 row created.
    
    SQL> insert into test (name) values ('Foot');
    
    1 row created.
    
    SQL> select * from test;
    
            ID NAME
    ---------- --------------------
             1 Little
             2 Foot
    

    Modify next value of the identity column:

    SQL> alter table test modify id generated by default on null as identity (start with 192734);
    
    Table altered.
    

    Some more testing:

    SQL> insert into test (name) values ('David');
    
    1 row created.
    
    SQL> select * from test;
    
            ID NAME
    ---------- --------------------
             1 Little
             2 Foot
        192734 David           --> here it is
    
    SQL>