A column can be declared as identity column. Each identity column is associated with a sequence generator. This generator supplies an increasing or decreasing integer value to the column if necessary.
CREATE TABLE tab (
…
col number GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ] AS IDENTITY [ identity options ]
…
);
If the sequence generator generates a value depends on the characteristics of the identity column. There are three main characteristics:
generated always
Oracle always provides a value. Value cannot be overridden or set in value(…) clause.
generated by default
Value is supplied if not explicitly overridden.
generated by default on null
Value only supplied if inserted value is null or omitted.
Note that the sequence generator does not guarantee that the values in the destination columns are unique. These values can be updated or sometimes overridden in the insert statement.
It should also be kept in mind that an identity column does not automatically create a primary key.
If a table is created with an inline primary key and an identity column, primary key follws the identity clause:
create table T (
id integer generated always as identity primary key,
…
);
Some (limited) information about identity columns can also be found in dba_tab_columns.
select
table_name,
column_name,
default_on_null,
data_default -- typically something like "RENE"."ISEQ$$_2235365".nextval
from
user_tab_columns
where
identity_column = 'YES' and
table_name like 'T_ID_%'
order by
table_name;
Simple test
The following simple test creates three tables each of which has an identity column to cover the possible three characteristics.
The example then inserts several records, sometimes using null, sometimes using a value for the identity column.
Finally, it selects from all three table in order to check which values are found in the identity columns.
drop table T_ID_1 purge;
drop table T_ID_2 purge;
drop table T_ID_3 purge;
create table T_ID_1 (id number generated always as identity, txt varchar2(20));
create table T_ID_2 (id number generated by default as identity, txt varchar2(20));
create table T_ID_3 (id number generated by default on null as identity, txt varchar2(20));
-- Cannot insert into ORA-32795: cannot insert into a generated always identity column
-- insert into T_ID_1 values (10 , 'ten' );
-- insert into T_ID_1 values (null, 'eleven' );
insert into T_ID_1 (txt) values ('twelve' );
insert into T_ID_1 (txt) values ('thirteen' );
insert into T_ID_2 values (2 , 'twenty' );
-- ORA-01400: cannot insert NULL into ("RENE"."T_ID_2"."ID")
-- insert into T_ID_2 values (null, 'twenty-one');
insert into T_ID_2 (txt) values ('twenty-two' );
insert into T_ID_2 (txt) values ('twenty-three');
insert into T_ID_3 values ( 3 , 'thirty' );
insert into T_ID_3 values (null, 'thirty-one' );
insert into T_ID_3 (txt) values ( 'thirty-two' );
insert into T_ID_3 values ( 'thirty-three');
insert into T_ID_3 (txt) values ( 'thirty-four' );
select * from T_ID_1;
--
-- ID TXT
-- ---------- --------------------
-- 1 twelve
-- 2 thirteen
select * from T_ID_2;
--
-- ID TXT
-- ---------- --------------------
-- 2 twenty
-- 1 twenty-two
-- 2 twenty-three
select * from T_ID_3;
--
-- ID TXT
-- ---------- --------------------
-- 3 thirty
-- 1 thirty-one
-- 2 thirty-two
-- 3 thirty-four
In order to create tables with identity columns, the create sequenceprivilege is required. Without that privilege, the error message ORA-01031: insufficient privileges error is thrown.