IDENTITY column autoincrement functionality in Oracle 12c

Tags

12c identity column, autoincrement, autonumber, identity column, primary key, sequence, trigger

Oracle 12c has introduced the IDENTITY column which is the automatic method over the old trigger-sequence based approach to auto-populate the primary key of a table. In my previous post, Auto-increment primary key in Pre 12c releases (Identity functionality) , I demonstrated the trigger-sequence based approach. now let’s see the new way in 12c –

TABLE with IDENTITY COLUMN

    SQL> CREATE TABLE t
      2    (
      3      ID NUMBER GENERATED ALWAYS AS IDENTITY
      4      START WITH 1000 INCREMENT BY 1,
      5      text VARCHAR2(50)
      6    );
    
    Table created.
    
    SQL>

INSERT

    SQL> INSERT INTO t
      2    ( text
      3    ) VALUES
      4    ( 'This table has an identity column'
      5    );
    
    1 row created.

    SQL> INSERT INTO t
      2    ( text
      3    ) VALUES
      4    ( 'This table has an identity column'
      5    );
    
    1 row created.
    
    SQL>

Let’s see if we have the ID column auto-incremented with the desired values-

    SQL> COLUMN text format A40
    SQL>
    SQL> SELECT * FROM t;
    
       ID TEXT
    ----- ----------------------------------------
     1000 This table has an identity column
     1001 This table has an identity column
    
    SQL>

So, the ID column now starts with value 1000 and increments by 1 with subsequent inserts.

Oracle creates a sequence to populate the identity column. You can find it named as ISEQ$$

    SQL>
    SQL> SELECT sequence_name,
      2    min_value,
      3    max_value,
      4    increment_by
      5  FROM user_sequences;
    
    SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY
    ------------------------------ ---------- ---------- ------------
    ISEQ$$_94087                            1 1.0000E+28            1
    
    SQL>

More information about the identity columns, use the ALL_TAB_IDENTITY_COLS view.

    SQL>
    SQL> SELECT table_name,
      2    column_name,
      3    generation_type,
      4    identity_options
      5  FROM all_tab_identity_cols
      6  WHERE owner = 'LALIT'
      7  ORDER BY 1,
      8    2;
    
    TABLE_NAME           COLUMN_NAME     GENERATION IDENTITY_OPTIONS
    -------------------- --------------- ---------- --------------------------------------------------
    
    T                    ID              ALWAYS     START WITH: 1000, INCREMENT BY: 1, MAX_VALUE:
                                                     9999999999999999999999999999, MIN_VALUE: 1, CYCLE
                                                    _FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N
    
    
    SQL>

Hope it helps!