CODEWITHSUNDEEP
oracle-database
PeaceIsPearl
PeaceIsPearl

Reputation: 339

Using IDENTITY for auto-generation of autonumeric field and replace sequence nextval

I have few legacy tables which use sequence to generate the primary key. I would want to use the auto generation feature using IDENTITY. How to make the change so that the legacy tables keep the same values for the original auto id sequence column.

Upvotes: 0

Views: 57

Answers (1)

Roberto Hernandez
Roberto Hernandez

Reputation: 8528

You can do something like this

SQL> create sequence seqemp start with 1 increment by 1 ;

Sequence created.

SQL> create table emp ( id number default seqemp.nextval , name varchar2(10) ) ;

Table created.

SQL> insert into emp ( name ) values ( 'AA' ) ;

1 row created.

SQL>  insert into emp ( name ) values ( 'AB' ) ;

1 row created.

SQL>  insert into emp ( name ) values ( 'AC' ) ;

1 row created.

SQL> select * from emp ;

        ID NAME
---------- ----------
         1 AA
         2 AB
         3 AC

You cannot replace the column by an identity because it is not supported. However you might add a new column as IDENTITY with the properties of the sequence

SQL> alter table emp add newid number generated by default as identity ( start with 1 increment by 1 ) ;
    
Table altered.

SQL> select * from emp ;

        ID NAME            NEWID
---------- ---------- ----------
         1 AA                  1
         2 AB                  2
         3 AC                  3

If you want to keep the original name:

  • Drop the column ID
  • Rename the column NEWID as ID

If you want the same order in the columns

  • Create a backup table with the order or the columns as you want
  • Drop original table
  • Rename backup table to original table

Of course, if you have constraints you will need to take care of them as well.

Upvotes: 1

Related Questions