Oracle 12c Identity Columns

Having a built-in mechanism to populate the identity columns was something that developers have been looking forward to as a feature in the Oracle database. But Oracle always provided and relied on the programmatic way of handling this. Developers used a sequence and a trigger combination to achieve the desired results. But finally Oracle has finally provided a built-in feature, Oracle 12c Database Identity Columns, to populate these Identity Columns. Let’s look at how you can use this new feature in Oracle 12c.

Oracle 12c Database Identity Columns

The syntax to use the Identity Columns feature in a table is pretty straight forward.

Column_Name Number (Data Type)
GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ] AS IDENTITY [ ( identity_options ) ]

Above you provide the column name with the data type set as a number. It is followed by the “GENERATED” keyword using either one of the three options – “ALWAYS”, “BY DEFAULT” or “BY DEFAULT ON NULL”. And finally there is the “AS IDENTITY” keyword, followed by the “IDENTITY_OPTIONS”. All these options refer to the characteristics of a sequence and are the same as were available previously, so we won’t discuss them in detail here.

Next, let’s look at these three Identity related options in a little more detail.

Using Identity Columns

First we will create a table which will has an Identity column, using the “ALWAYS” option. Before you can create the actual Identity Column, you will need to make sure that your user has the create sequence privilege.

SQL> create table idm_test (
2 id number generated ALWAYS as identity,
3 name varchar2(20));
Table created.”

Using the “ALWAYS” option forces the value for the column to ALWAYS use identity. Providing a custom value will result in an error.

SQL> insert into idm_test(name) values('John');
1 row created.

SQL> insert into idm_test(id,name) values(2,'John');
insert into idm_test(id,name) values(2,'John')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated ALWAYS identity column

If you change the identity from ALWAYS to BY DEFAULT then the identity value will be used in case there is no value provided in insert statement.

SQL> alter table idm_test modify id generated by default as identity;
Table altered.

This will force the table to accept values from insert statements as well and only provide the identity value if the insert statement does not provide one. However you cannot insert Null in the Identity Column and providing NULL in insert will result in error.

SQL> insert into idm_test(id,name) values(2,'John');
1 row created.

SQL> insert into idm_test(name) values('Mike');
1 row created.

SQL> insert into idm_test(id,name) values(NULL,'Test');
insert into idm_test(id,name) values(NULL,'Test')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("FOO"."IDM_TEST"."ID")

The last “By Default On Null” option forces the identity to be used only when the column is referenced in an insert statement and NULL is provided as the value.

SQL> alter table idm_test modify id generated by default on null as identity;
Table altered.

You can now reference an identity column in insert statements and pass in null values for the column. This column will have the value generated from the identity.

SQL> insert into idm_test(id,name) values(NULL,'Test');
1 row created.

How it Works and the Performance Enhacement

In order to create an identity you need to have the Create Sequence privilege. From this it is evident that Oracle creates a sequence in the background to populate the values. The following query confirms this.

SQL> select object_name,object_type
2 from user_objects;
OBJECT_NAME OBJECT_TYPE
------------------------- -----------------------
IDM_TEST TABLE
ISEQ$$_91701 SEQUENCE

You can expect a much better performance when using identity columns instead of when using the traditional sequence/trigger combination. This is because the later mentioned will have the overhead of running trigger with each insert.

Viewing Information

You can view more information on the Identity column in your database using the [DBA | ALL | USER ] _TAB_IDENTITY_COLS data dictionary view.

SQL> select table_name,column_name,generation_type
2 from USER_TAB_IDENTITY_COLS;

TABLE_NAME COLUMN_NAME GENERATION
-------------------- --------------- ----------
IDM_TEST ID BY DEFAULT

Restrictions on Use

While this feature simplifies the use of Identity values, there are some restrictions on the use of it.
• There can be only one Identity column per table.
• The column being used as identity should be of some numeric data type like Number.
• Identity columns cannot have default values.
• The NOT NULL and NOT DEFERRABLE constraints are implicit with Identity Column. Any inline constraint conflicting with these two will result in error.
• A strong encryption algorithm is recommended on Identity columns in case encryption is required.
• The Create table As Select statements will not inherit the identity property of a column.