Auto Increment Column in Oracle: New Features in Oracle Database 12cR1 (12.1)

Auto increment is now possible in Oracle Database with the help of sequence. This feature is now added in Oracle Database 12c Release 1. For this purpose we can use Default and Sequence.

We know, ‘DEFAULT’is used to specify default value when the column is not used in insert statement. And, Sequence is used to generate a number after a given interval. There are 2 pseudo columns CURRVAL and NEXTVAL for sequence to get current value and generate next value respectively.

In earlier version we can’t reference the sequence’s pseudo column with DEFAULT clause. For e.g.,

1.       Create a sequence.

        Create Sequence Test_SEQ;

2.       Create a table like this,

        Create Table Test_KS

    (ID        Number          Default    Test_SEQ.NextVal,

     Name      Varchar2(10)

    );

1^st statement will create a sequence with name Test_SEQ.

2^nd statement will display error:
        (ID        Number          Default    Test_SEQ.NextVal,
                                          *
    ERROR at line 2:
    ORA-00984: column not allowed here

But, in Oracle Database 12cR1 this will create a table. And now you can insert the record into this table as:

     Insert into Test_KS (Name) Values ('Kumar');

     Insert into Test_KS (Name) Values ('Sudhir');

     Insert into Test_KS (Name) Values ('Krishna');

     Insert into Test_KS (Name) Values ('Kant');

3.       When you select the record, you can find like,
          SQL> Select * From Test_KS;
                 ID  NAME
          ---------  ------------
                  1  Kumar
                  2  Sudhir
                  3  Krishna
                  4  Kant

          4 rows selected.

But Remember, in above table, ID is allowed null, hence, you can easily insert different value other than the next value in sequence and will no error. You must have to take care of value at the time of insertion.

     Insert into Test_KS (ID, Name) Values (445, 'Aarnav');

     Insert into Test_KS (ID, Name) Values (NULL, 'Anisha');

is not an error. Hence when you select the record next time, it will show like, 
        SQL> Select * From Test_KS;
               ID  NAME
        ---------  ----------
                1  Kumar
                2  Sudhir
                3  Krishna
                4  Kant
              445  Aarnav
                   Anisha

        6 rows selected.

Note: DEFAULT clause works only when no value is passed in insert statement for the column.

You can use CURVAL in DEFAULT clause too. Example,

1.      Create a sequence

        Create Sequence Test_Add_SEQ;

2.       Create table as,

   Create Table Test_KS_Add

   (ADD_ID        Number     Default  Test_Add_SEQ.NextVal,

    Name_ID       Number     Default  Test_Seq.CurrVal,

    Address       Varchar2(20)

   );

3.       Check by inserting value in this table as,

       Insert into Test_KS (Name) Values  ('Anjali');

   Insert into Test_KS_Add (Address) values ('DELHI');

   Insert into Test_KS (Name) Values ('Rai');

   Insert into Test_KS_Add (Address) values ('PATNA');

        SQL> Select * From Test_KS;
               ID  NAME
        ---------  ----------
                1  Kumar
                2  Sudhir
                3  Krishna
                4  Kant
              445  Aarnav
                   Anisha
                5  Anjali
                6  Rai

        8 rows selected.

        SQL> Select * From Test_KS_Add;

           ADD_ID    NAME_ID  ADDRESS
        ---------  ---------  ------------
                1          1  DELHI
                2          6  PATNA

        2 rows selected.

Enjoy!!!

Must visit World's Local Business Search Engine: www.yougets.com