Oracle Identity Columns in 12c

Identity columns are a new feature in Oracle 12c. We need the CREATE SEQUENCE privilege for using this feature. The syntax is given below.

GENERATED
 [ ALWAYS | BY DEFAULT [ ON NULL ] ]
 AS IDENTITY

There are two options to create an identity column, As mentioned ALWAYS clause will always populate the column automatically. Any attempt to insert a value (Even NULL) will throw an error. BY DEFAULT /ON NULL  will populate only if we don’t specify the value or we try to insert a null value. Otherwise it will insert the value we specify in our insert statement.

Lets check how this feature works. At first we will create the table with ALWAYS option.

SQL> create table my_tab
 2 (
 3 name varchar2(50),
 4 id integer GENERATED ALWAYS AS IDENTITY
 5 );

Table created.

We have created the column id as IDENTITY column and which will be always populated by oracle. It wont let us insert a value/NULL in that column.

SQL> insert into my_tab (name) values ('John');

1 row created.

SQL> insert into my_tab values ('Mike', 2);
 insert into my_tab values ('Mike', 2)
 *
 ERROR at line 1:
 ORA-32795: cannot insert into a generated always identity column

SQL> insert into my_tab values ('Steve', NULL);
 insert into my_tab values ('Steve', NULL)
 *
 ERROR at line 1:
 ORA-32795: cannot insert into a generated always identity column

SQL> insert into my_tab (name) values ('Steve');

1 row created.


SQL> select * from my_tab;

NAME                                               ID
-------------------------------------------------- ----------
John                                               1
Steve                                              2

Now lets recreate the table with DEFAULT option.

SQL> create table my_tab
 2 (
 3 name varchar2(20),
 4 id integer GENERATED BY DEFAULT ON NULL AS IDENTITY
 5 );

Table created.

SQL> insert into my_tab values ('Tom', 1);

1 row created.


SQL> insert into my_tab (name) values ('James');

1 row created.

SQL> insert into my_tab values ('Nick', NULL);

1 row created.

SQL> select * from my_tab;

NAME                 ID
-------------------- ----------
Tom                  1
James                1
Nick                 2

DEFAULT clause will insert the value if we don’t specify in the INSERT statement or we try to insert a NULL value.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s