LATEST TOPICS

Oracle Database 12c New feature: Identity column or Auto-increment column

Introduction:

Prior to Oracle database 12c, there was no direct method available to auto increment a column value in a oracle database table. We had to implement triggers and generate sequence values based on those triggers and then use the sequences to auto increment the column values.

However, staring from Oracle Database 12c; Oracle has provided not just one but two methods to help auto increment a column value for tables.

In today’s post, I would be discussing one of these two features called IDENTITY COLUMN.

What is a Identity Column?

An Identity Column is a column (also known as a field) in a database table that is made up of values generated by the database ( this is what Wiki says)

An identity column is a column that stores numbers and which gets incremented with each insertion.
Identity columns are sometimes also referred as auto-increment columns.

 

How to Implement Identity column for a Oracle database table?

Oracle has introduced a new CLAUSE for a table column definition as mentioned below.

GENERATED
[
ALWAYS | BY DEFAULT [ ON NULL ]
]
AS IDENTITY [ ( identity_options ) ]

 

With the help of the clause ‘GENERATED…AS IDENTITY‘ we define a Identity Column for a Oracle database table.

As per the new clause, we have three options available for a Identity Column definition.

GENERATED ALWAYS AS IDENTITY [(IDENTITY_OPTIONS)]
GENERATED BY DEFAULT AS IDENTITY [(IDENTITY_OPTIONS)]
GENERATED BY DEFAULT ON NULL AS IDENTITY [(IDENTITY_OPTIONS)]

We will get to know these different options through the demonstration.

Demonstration

CLAUSE 1: GENERATED ALWAYS AS IDENTITY

For now, I am skipping the optional (IDENTITY_OPTIONS) properties from the clause. I will brief about it later in this section

Okay, as part of the first method, lets create a table with Identity Column option GENERATED ALWAYS AS IDENTITY

SQL:labpa>  create table identity_tst_tab (
  2  id number(10) GENERATED AS IDENTITY,
  3  name varchar(15)
  4  );

Table created.

Lets DESCRIBE the table to see , if there is anything new for us.

SQL:labpa>  desc IDENTITY_TST_TAB
  Name                    Null?    Type
 ----------------------- -------- ----------------
 ID                      NOT NULL NUMBER(10)
 NAME                             VARCHAR2(15)

Now, if we check the description of the table; we can see a NOT NULL constraint is automatically imposed on the Identity column ( we have defined NOT NULL as part of table creation). Which means IDENTITY COLUMN can not be NULL.

Lets try to insert few records into the table.

SQL:labpa>  insert into identity_tst_tab (name) values ('abbas');

1 row created.

SQL:labpa>  insert into IDENTITY_TST_TAB (name) values ('fazal');

1 row created.

SQL:labpa>  select * from identity_tst_tab;

        ID NAME
---------- ---------------
         1 abbas
         2 fazal

As expected, the value got populated automatically.

Now, try to manually insert value for the Identity Column (ID).

SQL:labpa>  insert into identity_tst_tab  values (100,'xyz');
insert into identity_tst_tab  values (100,'xyz')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

We are not allowed to manually insert value for the Identity Column. That is because of the GENERATED ALWAYS clause that we have opted for the Identity Column. i.e. when GENERATED ALWAYS  clause is used for a Identity Column, it will ensure that only system generated auto increment values are getting inserted into the Identity Column.

Now, let see how Oracle actually populates the auto-increment values for the Identity column.

SQL:labpa>  explain plan for insert into identity_tst_tab (name) values ('fazal');

Explained.

SQL:labpa>  @?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 2458898149

---------------------------------------------------------------------------------------------
| Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                  |     1 |   100 |     1   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | IDENTITY_TST_TAB |       |       |            |          |
|   2 |   SEQUENCE               | ISEQ$$_92697     |       |       |            |          |
---------------------------------------------------------------------------------------------

9 rows selected.

Seems like, Oracle is internally using sequence to populate the auto-increment column values.

Lets get the DDL of the table to crosscheck.

SQL:labpa>  select dbms_metadata.get_ddl('TABLE','IDENTITY_TST_TAB','RND_USER') DDL from dual;

DDL
----------------------------------------------------------------------------------------------------
CREATE TABLE "RND_USER"."IDENTITY_TST_TAB"
(       "ID" NUMBER(10,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999
INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOT NULL ENABLE,
"NAME" VARCHAR2(15)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "APP_DATA"

Yes, it is using sequence (if you look at the MINVALUE 1 MAXVALUE…INCREMENT BY… line from the DDL, it is similar to the syntax of a sequence) . That means when we define a Identity Column, Oracle internally creates a sequence and attach it to the table column.

Lets confirm the use of sequence from DBA_SEQUENCES

SQL:labpa>  select SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY from dba_sequences where sequence_name='ISEQ$$_92697';

SEQUENCE_NAME    MIN_VALUE                     MAX_VALUE INCREMENT_BY
--------------- ---------- ----------------------------- ------------
ISEQ$$_92697             1  9999999999999999999999999999            1

Now, here is the relation. Oracle internally creates a sequence with name ISEQ$$_[table_object_id]

SQL:labpa>  select object_name,object_type,object_id from dba_objects where object_id=92697;

OBJECT_NAME          OBJECT_TYPE              OBJECT_ID
-------------------- ----------------------- ----------
IDENTITY_TST_TAB     TABLE                        92697

Now coming back to optional parameters IDENTITY_OPTIONS of the new GENERATED..AS IDENTITY clause for a table column, with the help of IDENTITY_OPTIONS we can define all the properties (mentioned below) of sequence to the Identity Column

{ START WITH ( integer | LIMIT VALUE )
| INCREMENT BY integer
| ( MAXVALUE integer | NOMAXVALUE )
| ( MINVALUE integer | NOMINVALUE )
| ( CYCLE | NOCYCLE )
| ( CACHE integer | NOCACHE )
| ( ORDER | NOORDER ) }...

For an example, let me create a Identity Column that starts with a value 1000 and increments by 10.

SQL:labpa>  create table IDENTITY_TST_TAB_IO (
  2  id number(10) GENERATED ALWAYS AS IDENTITY START WITH 1000 INCREMENT BY 10 MAXVALUE 999999999,
  3  name varchar(10)
  4  );

Table created.

SQL:labpa>  insert into IDENTITY_TST_TAB_IO (name) values ('abbas');

1 row created.

SQL:labpa>  insert into IDENTITY_TST_TAB_IO (name) values ('fazal');

1 row created.

SQL:labpa>  select * from IDENTITY_TST_TAB_IO;

        ID NAME
---------- ----------
      1000 abbas
      1010 fazal

 

If IDENTITY_OPTIONS are not provided, oracle creates the internal sequence with starting value of 1 that increments by 1 and can to a maximum value of system’s allowed limit

CLAUSE 2: GENERATED BY DEFAULT AS IDENTITY

As part of this exercise, lets create a table with the Identity Column clause GENERATED BY DEFAULT AS IDENTITY

SQL:labpa>  create table identity_tst_tab_1 (
  2  id number(20) GENERATED BY DEFAULT AS IDENTITY,
  3  name varchar(20)
  4  );

Table created.

DESCRIBE the table to crosscheck its structure

SQL:labpa>  desc identity_tst_tab_1
 Name                    Null?    Type
 ----------------------- -------- ----------------
 ID                      NOT NULL NUMBER(20)
 NAME                             VARCHAR2(20)

As with the GENERATED ALWAYS clause, NOT NULL constraint is also imposed here automatically.

Lets insert few records into the table

SQL:labpa>  insert into identity_tst_tab_1 values (100,'abbas');

1 row created.

SQL:labpa>  insert into identity_tst_tab_1 (name) values ('fazal');

1 row created.

SQL:labpa>  insert into identity_tst_tab_1 values (110,'abc');

1 row created.

SQL:labpa>  insert into identity_tst_tab_1 (name) values ('xyz');

1 row created.

SQL:labpa>  select * from identity_tst_tab_1;

        ID NAME
---------- --------------------
       100 abbas
         1 fazal
       110 abc
         2 xyz

Unlike in GENERATED ALWAYS clause, we can have manual insertion for the Identity Column values with GENERATED BY DEFAULT clause

When we do not specify any value for the Identify column, the internal sequence come into play to auto populate the value

SQL:labpa>  explain plan for insert into identity_tst_tab_1 (name) values ('xyz');

Explained.

SQL:labpa>  @?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1018648623

-----------------------------------------------------------------------------------------------
| Id  | Operation                | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                    |     1 |   100 |     1   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | IDENTITY_TST_TAB_1 |       |       |            |          |
|   2 |   SEQUENCE               | ISEQ$$_92699       |       |       |            |          |
-----------------------------------------------------------------------------------------------

9 rows selected.

When, we manually specify the value for Identity column, the Internal Sequence is ignored.

SQL:labpa>  explain plan for insert into identity_tst_tab_1 values (110,'abc');

Explained.

SQL:labpa>  @?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------
| Id  | Operation                | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                    |     1 |   100 |     1   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | IDENTITY_TST_TAB_1 |       |       |            |          |
-----------------------------------------------------------------------------------------------

7 rows selected.

CLAUSE 3: GENERATED BY DEFAULT ON NULL AS IDENTITY

Lets create a table with GENERATED BY DEFAULT ON NULL AS IDENTITY clause

SQL:labpa>  create table identity_tst_tab_2 (
  2  id number(10) GENERATED BY DEFAULT ON NULL AS IDENTITY,
  3  name varchar(15)
  4  );

Table created.

Lets DESCRIBE the table

SQL:labpa>  desc identity_tst_tab_2
 Name                    Null?    Type
 ----------------------- -------- ----------------
 ID                      NOT NULL NUMBER(10)
 NAME                             VARCHAR2(15)

NOT NULL constraint is also imposed here automatically like the other two available clauses. Therefor, it is confirmed that with any of IDENTITY COLUMN clause we can not have NULL values.

Lets try to insert few records.

SQL:labpa>  insert into identity_tst_tab_2 values (NULL,'abbas');

1 row created.

SQL:labpa>  insert into identity_tst_tab_2 values (200,'fazal');

1 row created.

SQL:labpa>  insert into identity_tst_tab_2 (name) values ('abc');

1 row created.

SQL:labpa>  select * from identity_tst_tab_2;

        ID NAME
---------- ---------------
         1 abbas
       200 fazal
         2 abc

So, with GENERATED BY DEFAULT ON NULL clause, Oracle will also auto populate the identity column for NULL values as well as this method allows manually populating the Identity Column value.

When NULL or no value is specified for the IDENTITY COLUMN, the internal sequence comes into picture and when a values is explicitly specified the internal sequence is ignored.

 

Altering Identity Columns

We can alter identity column properties only for a identity column itself i.e. we can not impose identity column properties for a column which was not defined as identity column.

SQL:labpa>  select TABLE_NAME,COLUMN_NAME,GENERATION_TYPE,IDENTITY_OPTIONS from DBA_TAB_IDENTITY_COLS where table_name='IDENTITY_TST_TAB';

TABLE_NAME          COLUMN_NAM GENERATION IDENTITY_OPTIONS
------------------- ---------- ---------- -------------------------------------------------------------------------------------
IDENTITY_TST_TAB    ID         ALWAYS     START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999999999999999999999999, MIN_VALUE: 1
                                          , CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N

SQL:labpa>  alter table IDENTITY_TST_TAB modify (ID number(10) GENERATED BY DEFAULT AS IDENTITY);

Table altered.

SQL:labpa>  select TABLE_NAME,COLUMN_NAME,GENERATION_TYPE,IDENTITY_OPTIONS from DBA_TAB_IDENTITY_COLS where table_name='IDENTITY_TST_TAB';

TABLE_NAME          COLUMN_NAM GENERATION IDENTITY_OPTIONS
------------------- ---------- ---------- -------------------------------------------------------------------------------------
IDENTITY_TST_TAB    ID         BY DEFAULT START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999999999999999999999999, MIN_VALUE: 1
                                          , CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N

SQL:labpa>  alter table IDENTITY_TST_TAB modify (ID number(10) GENERATED BY DEFAULT AS IDENTITY INCREMENT BY 100);

Table altered.

SQL:labpa>  select TABLE_NAME,COLUMN_NAME,GENERATION_TYPE,IDENTITY_OPTIONS from DBA_TAB_IDENTITY_COLS where table_name='IDENTITY_TST_TAB';

TABLE_NAME          COLUMN_NAM GENERATION IDENTITY_OPTIONS
------------------- ---------- ---------- -------------------------------------------------------------------------------------
IDENTITY_TST_TAB    ID         BY DEFAULT START WITH: 1, INCREMENT BY: 100, MAX_VALUE: 9999999999999999999999999999, MIN_VALUE:
                                           1, CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N

Trying to alter a non Identity Column as a Identity column will lead to erorrs

SQL:labpa>  alter table EMP_INFO modify (EMP_ID NUMBER(10) GENERATED BY DEFAULT AS IDENTITY INCREMENT BY 100);
alter table EMP_INFO modify (EMP_ID NUMBER(10) GENERATED BY DEFAULT AS IDENTITY INCREMENT BY 100)
                             *
ERROR at line 1:
ORA-30673: column to be modified is not an identity column

On the contrary, we can convert a Identity Column to a non Identity column as follows

SQL:labpa>  select table_name,has_identity from dba_tables where table_name='IDENTITY_TST_TAB';

TABLE_NAME          HAS
------------------- ---
IDENTITY_TST_TAB    YES

SQL:labpa>  alter table IDENTITY_TST_TAB modify ID DROP IDENTITY;

Table altered.

SQL:labpa>  select table_name,has_identity from dba_tables where table_name='IDENTITY_TST_TAB';

TABLE_NAME          HAS
------------------- ---
IDENTITY_TST_TAB    NO

This will update the table definition to remove the identity clause as well as will drop the associated sequence for that identity column.

SQL:labpa>  select object_id from dba_objects where object_name='IDENTITY_TST_TAB';

 OBJECT_ID
----------
     92697

SQL:labpa>  select sequence_name from dba_sequences where sequence_name like '%92697%';

no rows selected

 

Dropping tables defined with identity column

Dropping a table defined with identity column also drops the sequence attached to it. Hence, we do not need to perform manual cleanup of unused sequences.

SQL:labpa>  select object_id from dba_objects where object_name='IDENTITY_TST_TAB_IO';

 OBJECT_ID
----------
     92717

SQL:labpa>  select sequence_name from user_sequences where SEQUENCE_NAME='ISEQ$$_92717';

SEQUENCE_NAME
---------------
ISEQ$$_92717

SQL:labpa>  drop table IDENTITY_TST_TAB_IO purge;

Table dropped.

SQL:labpa>  select sequence_name from user_sequences where SEQUENCE_NAME='ISEQ$$_92717';

no rows selected

 

Views to query IDENTITY COLUMNS

We can query [ALL]/[USER]/[DBA]_TAB_IDENTITY_COLS to check the identity columns defined for all the tables.

SQL:labpa>  select TABLE_NAME,COLUMN_NAME,GENERATION_TYPE,IDENTITY_OPTIONS from DBA_TAB_IDENTITY_COLS;

TABLE_NAME          COLUMN_NAM GENERATION IDENTITY_OPTIONS
------------------- ---------- ---------- -------------------------------------------------------------------------------------
IDENTITY_TST_TAB    ID         ALWAYS     START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999999999999999999999999, MIN_VALUE: 1
                                          , CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N

IDENTITY_TST_TAB_1  ID         BY DEFAULT START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999999999999999999999999, MIN_VALUE: 1
                                          , CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N

IDENTITY_TST_TAB_2  ID         BY DEFAULT START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999999999999999999999999, MIN_VALUE: 1
                                          , CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N

We can also query [ALL]/[USER]/[DBA]_TABLES and to check if a table is defined with Identity Column

SQL:labpa>  select table_name,HAS_IDENTITY from dba_tables where table_name='IDENTITY_TST_TAB';

TABLE_NAME          HAS
------------------- ---
IDENTITY_TST_TAB    YES

We can also query [ALL]/[USER]/[DBA]_TAB_COL to check the Identity Column for a table along with its default value

SQL:labpa>  select table_name,COLUMN_NAME,DATA_DEFAULT from dba_tab_cols where table_name='IDENTITY_TST_TAB' and IDENTITY_COLUMN='YES';

TABLE_NAME          COLUMN_NAM DATA_DEFAULT
------------------- ---------- -----------------------------------
IDENTITY_TST_TAB    ID         "RND_USER"."ISEQ$$_92697".nextval

Conclusion

With the introduction of Identity Column, Oracle has provided the ability to auto increment column values. We also have option to choose the limit of the numbers for the column and to decide on the increment.

Further, we have the flexibility to choose between the three type of Identity columns (demonstrated in this post) depending on our requirement.

However, there are certain restrictions while using Identity column such as the column value can not be NULL, only numbers are allowed for Identity Column, etc.

For a complete list of restrictions please refer here

 

Follow up:

In my next post, I would be discussing the other feature introduced in Oracle Database 12c for auto increment of column values.

3 Comments
  1. Daniel
  2. jaya singh
    • Abu Fazal Abbas
%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed