What Is Oracle’s Identity Column?
Did you know about a database feature that was introduced with 12c, but is not widely used?
This feature would make the life of developers and DBAs much easier: the introduction of the identity columns.
What is an identity column?
The Identity Column is a system generated, auto-incrementing column, used to populate primary keys, unique key, and regular columns. This means that your column doesn’t have to be a primary key to be able to create it as an identity.
In previous versions you had to use a sequence and potentially a trigger to achieve the same result. Whenever a new row would be inserted, the trigger would fire and insert the next value of the sequence in the column. With this method, there was no connection between the sequence and table. The sequence could be used to populate multiple tables.
But let’s not talk about the old way of doing things, which you already know, instead, let’s see what the identity column is and how to use it!
When you create a table, you can define an identity clause on a numeric type column, which would define the column as an identity column. When you insert a new row in such a table, Oracle will auto-generate and insert that value into the column. Behind the scene, not only a table is created, but also a sequence is created. Because of this, the user who creates the table with the identity column, must also have the create sequence privilege, beside the create table privilege.
There are 3 options you can specify for the identity column:
GENERATED [ ALWAYS | BY DEFAULT | BY DEFAULT ON NULL ] AS IDENTITY [(more identity options)]
As you can see the GENERATED keyword is mandatory. What do the other options mean?
ALWAYS – forces the use of identity column values, the sequence generator will always assign the value. Attempting to explicitly assign a value or a NULL, will result in an error. This is the default option. Let’s see the example below:
create table test_identity_always (
id number generated always as identity,
name varchar2(20));
Table created.
insert into test_identity_always (name)
values ('First row');
1 row created.
insert into test_identity_always (id,name)
values (2,'Second row');
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
insert into test_identity_always (id,name)
values (NULL,'Third row');
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
select * from test_identity_always;
ID NAME
---------- -------------------------------
1 First row
BY DEFAULT – allows you to use the sequence generator or provide your own value explicitly to populate the column. You cannot specify NULL as a value, as it will generate an error. Example below:
create table test_identity_default (
id number generated by default as identity,
name varchar2(20));
Table created.
insert into test_identity_default (name)
values ('First row');
1 row created.
insert into test_identity_default (id,name)
values (22,'Second row');
1 row created.
insert into test_identity_default (id,name)
values (NULL,'Third row');
ORA-01400: cannot insert NULL into ("DIANA"."TEST_IDENTITY_DEFAULT"."ID")
select * from test_identity_default;
ID NAME
---------- -------------------------------
1 First row
22 Second row
BY DEFAULT ON NULL – allows you to use the sequence generator or provide your own value explicitly to populate the column. You can also specify NULL as a value, however the sequence generator will replace the value for that. Example below:
create table test_identity_default_null (
id number generated by default on null as identity,
name varchar2(20));
Table created.
insert into test_identity_default_null (name)
values ('First row');
1 row created.
insert into test_identity_default_null (id,name)
values (22,'Second row');
1 row created.
insert into test_identity_default_null (id,name)
values (NULL,'Third row');
1 row created.
select * from test_identity_default_null;
ID NAME
---------- -------------------------------
1 First row
22 Second row
2 Third row
There are more identity options that can be defined for the column, just like you would have for a sequence. The identity options clause would configure the sequence generator. You have a START WITH , INCREMENT BY, MAXVALUE, CYCLE, CACHE, ORDER and so on, same meanings as you have for a sequence. After all, behind the scene a sequence is created.
The view to check for tables with identity columns is DBA_TAB_IDENTITY_COLS. Here you will see the table name, column name, sequence name and the details about the sequence.
set linesize 200
col table_name for A26
col column for A6
col generation for A10
col identity_options for A50
col sequence_name for A15
select table_name, column_name as column,
generation_type as generation,
sequence_name,identity_options
from dba_tab_identity_cols
where owner='DIANA';
TABLE_NAME COLUMN GENERATION SEQUENCE_NAME IDENTITY_OPTIONS
-------------------------- ------ ---------- --------------- --------------------------------------------------
TEST_IDENTITY_ALWAYS ID ALWAYS ISEQ$$_148506 START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999
999999999999999999999, MIN_VALUE: 1, CYCLE_FLAG: N
, CACHE_SIZE: 20, ORDER_FLAG: N
TEST_IDENTITY_DEFAULT ID BY DEFAULT ISEQ$$_148508 START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999
999999999999999999999, MIN_VALUE: 1, CYCLE_FLAG: N
, CACHE_SIZE: 20, ORDER_FLAG: N
TEST_IDENTITY_DEFAULT_NULL ID BY DEFAULT ISEQ$$_148512 START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999
999999999999999999999, MIN_VALUE: 1, CYCLE_FLAG: N
, CACHE_SIZE: 20, ORDER_FLAG: N
You can also check DBA_SEQUENCES to confirm the sequences that got created:
select sequence_name from dba_sequences where sequence_owner='DIANA';
SEQUENCE_NAME
---------------
ISEQ$$_148506
ISEQ$$_148508
ISEQ$$_148512
What do you think happens if you drop the table, will the sequence be dropped as well? You bet! See below there are only two sequences left, after dropping the table.
drop table TEST_IDENTITY_DEFAULT_NULL purge;
select sequence_name from dba_sequences where sequence_owner='DIANA';
SEQUENCE_NAME
---------------
ISEQ$$_148506
ISEQ$$_148508
Can you drop the sequence without dropping the table? No, you cannot drop it. The sequence is linked to the table:
drop sequence diana.ISEQ$$_148506;
drop sequence diana.ISEQ$$_148506
*
ERROR at line 1:
ORA-32794: cannot drop a system-generated sequence
If you drop the identity column, what happens to the sequence? The sequence is also dropped:
alter table TEST_IDENTITY_ALWAYS drop column id;
Table altered.
select sequence_name from dba_sequences where sequence_owner='DIANA';
SEQUENCE_NAME
---------------
ISEQ$$_148508
There are some constraints or limitations for the identity columns:
- only one identity column per table
- the identity column data type can only be numeric, and it cannot have a default value.
- when you create the identity column, a not null, not deferrable constraint is also created.
- if you create a table with the CTAS method, then the new table will not inherit the identity column.
Have you used this feature so far? What do you think of it?
If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!
If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get Today‘s Special, only $13.99 CAD !
