Identity columns were introduced in Oracle 12c, this new
feature allows you to specify that a column should be automatically populated
from a system created sequence.
First - an example of a table without an identity column.
demo@ORA12C> create table transaction1(
2 transaction_id int,
3 holding_id int,
4 transaction_type number,
5 transaction_date date,
6 credit_value number,
7 debit_value number );
Table created.
demo@ORA12C> desc transaction1;
Name Null? Type
----------------------------------- --------
-----------
TRANSACTION_ID NUMBER(38)
HOLDING_ID NUMBER(38)
TRANSACTION_TYPE NUMBER
TRANSACTION_DATE DATE
CREDIT_VALUE NUMBER
DEBIT_VALUE NUMBER
demo@ORA12C>
In its simples form, the Identity clause can be specified like this.
demo@ORA12C> create table transaction2(
2 transaction_id int GENERATED AS IDENTITY,
3 holding_id int,
4 transaction_type number,
5 transaction_date date,
6 credit_value number,
7 debit_value number );
Table created.
demo@ORA12C>
demo@ORA12C> desc transaction2;
Name Null? Type
----------------------------------- --------
-------------
TRANSACTION_ID NOT NULL NUMBER(38)
HOLDING_ID NUMBER(38)
TRANSACTION_TYPE NUMBER
TRANSACTION_DATE DATE
CREDIT_VALUE NUMBER
DEBIT_VALUE NUMBER
demo@ORA12C>
Note that a NOT NUL constraint has been automatically created for the
TRANSACTION_ID column.
A sequence is created with a system created name
ISEQ$$_<object_id>, where object_id is the object_id of the table.
demo@ORA12C> select object_id
2 from user_objects
3 where object_name ='TRANSACTION2' ;
OBJECT_ID
----------
87436
demo@ORA12C>
demo@ORA12C> select SEQUENCE_NAME from user_sequences ;
SEQUENCE_NAME
---------------
ISEQ$$_87436
demo@ORA12C>
By default, the GENERATED AS IDENTITY clause implicitly include the ALWAYS
keyword, i.e GENERATED ALWAYS AS IDENTITY.
When the ALWAYS keyword is specified it is not possible to explicitly
include values for the identity column in INSERT or UPDATE statement, doing so
will produce error like this.
demo@ORA12C> insert into
transaction2(transaction_id,holding_id,
2
transaction_type,transaction_date,credit_value,debit_value)
3 values(55,1,0,sysdate,0,2);
insert into transaction2(transaction_id,holding_id,
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always
identity column
Values can also be generated automatically if no values are specified
explicitly, using the following syntax.
demo@ORA12C> create table transaction3(
2 transaction_id int GENERATED BY DEFAULT AS IDENTITY,
3 holding_id int,
4 transaction_type number,
5 transaction_date date,
6 credit_value number,
7 debit_value number );
Table created.
Unlike the GENERATED AS IDENTITY clause, using GENERATED BY DEFAULT AS
IDENTITY we can insert the transaction_id column explicitly like this.
demo@ORA12C> insert into
transaction3(transaction_id,holding_id,
2
transaction_type,transaction_date,credit_value,debit_value)
3 values(55,1,0,sysdate,0,2);
1 row created.
Or we could ignore the transaction_id column, where the database would
take care of populating the values into this IDENTITY column.
demo@ORA12C> insert into transaction3(holding_id,
2
transaction_type,transaction_date,credit_value,debit_value)
3 values(1,0,sysdate,0,2);
1 row created.
demo@ORA12C> select * from transaction3 ;
TRANSACTION_ID HOLDING_ID TRANSACTION_TYPE TRANSACTION
CREDIT_VALUE DEBIT_VALUE
-------------- ---------- ---------------- -----------
------------ -----------
55 1 0 31-JUL-2017 0 2
1 1 0 31-JUL-2017 0
2
demo@ORA12C>
If you wish values to be generated when NULL value is supplied use GENERATED
BY DEFAULT AS IDENTITY ON NULL option.
demo@ORA12C> create table transaction4(
2 transaction_id int GENERATED BY DEFAULT ON NULL AS IDENTITY,
3 holding_id int,
4 transaction_type number,
5 transaction_date date,
6 credit_value number,
7 debit_value number );
Table created.
With that, we can explicitly insert the values like this.
demo@ORA12C> insert into transaction4(transaction_id,holding_id,
2
transaction_type,transaction_date,credit_value,debit_value)
3 values(55,1,0,sysdate,0,2);
1 row created.
demo@ORA12C> select * from transaction4;
TRANSACTION_ID HOLDING_ID TRANSACTION_TYPE TRANSACTION
CREDIT_VALUE DEBIT_VALUE
-------------- ---------- ---------------- -----------
------------ -----------
55 1 0 31-JUL-2017 0 2
Else have an explicit NULL, and then the database will manipulate them
for IDENTITY columns.
demo@ORA12C> insert into
transaction4(transaction_id,holding_id,
2
transaction_type,transaction_date,credit_value,debit_value)
3 values(NULL,1,0,sysdate,0,2);
1 row created.
demo@ORA12C> select * from transaction4;
TRANSACTION_ID HOLDING_ID TRANSACTION_TYPE TRANSACTION
CREDIT_VALUE DEBIT_VALUE
-------------- ---------- ---------------- -----------
------------ -----------
55 1 0 31-JUL-2017 0 2
1 1 0 31-JUL-2017 0 2
Else have an implicit NULL, and then the database will manipulate them
for IDENTITY columns.
demo@ORA12C> insert into transaction4(holding_id,
2
transaction_type,transaction_date,credit_value,debit_value)
3 values(1,0,sysdate,0,2);
1 row created.
demo@ORA12C> select * from transaction4;
TRANSACTION_ID HOLDING_ID TRANSACTION_TYPE TRANSACTION
CREDIT_VALUE DEBIT_VALUE
-------------- ---------- ---------------- -----------
------------ -----------
55 1 0 31-JUL-2017 0 2
1 1 0 31-JUL-2017 0 2
2 1 0 31-JUL-2017 0 2
demo@ORA12C>
We can check which tables have IDENTITY columns from user_tables data
dictionary.
demo@ORA12C> select table_name ,has_identity
2 from user_tables
3 where table_name like 'TRANSACTIO%'
4 order by 1;
TABLE_NAME HAS
--------------- ---
TRANSACTION1 NO
TRANSACTION2 YES
TRANSACTION3 YES
TRANSACTION4 YES
demo@ORA12C>
The ALTER Table statement supports the IDENTITY clause, but you cannot
convert a non-identity column to an identity column.
Converting a non-identity column to identity column will return this
error.
demo@ORA12C> alter table transaction1
2 modify transaction_id GENERATED AS
IDENTITY ;
modify transaction_id GENERATED AS IDENTITY
*
ERROR at line 2:
ORA-30673: column to be modified is not an identity column
Using ALTER Table statement, you can modify BY DEFAULT ON NULL clause
into an ALWAYS clause.
demo@ORA12C> create table transaction5(
2 transaction_id int GENERATED BY DEFAULT ON NULL AS IDENTITY primary key,
3 holding_id int,
4 transaction_type number,
5 transaction_date date,
6 credit_value number,
7 debit_value number );
Table created.
demo@ORA12C> insert into
transaction5(transaction_id,holding_id,
2
transaction_type,transaction_date,credit_value,debit_value)
3 values(1,1,0,sysdate,0,2);
1 row created.
demo@ORA12C> insert into
transaction5(transaction_id,holding_id,
2
transaction_type,transaction_date,credit_value,debit_value)
3 values(2,1,0,sysdate,0,2);
1 row created.
demo@ORA12C> insert into
transaction5(transaction_id,holding_id,
2
transaction_type,transaction_date,credit_value,debit_value)
3 values(3,1,0,sysdate,0,2);
1 row created.
demo@ORA12C> alter table transaction5 modify transaction_id
2 GENERATED ALWAYS AS IDENTITY (start with limit value) ;
Table altered.
demo@ORA12C> insert into transaction5(holding_id,
2
transaction_type,transaction_date,credit_value,debit_value)
3 values(1,0,sysdate,0,2);
1 row created.
demo@ORA12C> select * from transaction5 ;
TRANSACTION_ID HOLDING_ID TRANSACTION_TYPE TRANSACTION
CREDIT_VALUE DEBIT_VALUE
-------------- ---------- ---------------- -----------
------------ -----------
1 1 0 31-JUL-2017 0 2
2 1 0 31-JUL-2017 0 2
3 1 0 31-JUL-2017 0 2
4 1 0 31-JUL-2017 0 2
demo@ORA12C>
START WITH LIMIT VALUE, can only be used with ALTER TABLE MODIFY. If
you specify START WITH LIMIT VALUE, then Oracle Database locks the table and
finds the maximum identity column value in the table and assigns the value as
the sequence generator's high water mark.
While we cannot change a non-identity column into an identity column,
we can convert an identity column into a non- identity column.
demo@ORA12C> alter table transaction5
2 modify transaction_id
3 drop identity ;
Table altered.
demo@ORA12C> select table_name ,has_identity
2 from user_tables
3 where table_name ='TRANSACTION5';
TABLE_NAME HAS
--------------- ---
TRANSACTION5 NO
demo@ORA12C>
A Table cannot have more than one IDENTITY column.
demo@ORA12C> alter table transaction2
2 add holding_id2 int
3 generated always as identity;
add holding_id2 int
*
ERROR at line 2:
ORA-30669: table can have only one identity column
IDENTITY column cannot be possible on non-numeric datatypes.
demo@ORA12C> alter table transaction
2 add holding_id2 varchar2(10)
3 generated always as identity;
generated always as identity
*
ERROR at line 3:
ORA-30675: identity column must be a numeric type
IDENTITY column cannot have Default values.
demo@ORA12C> alter table transaction2
2 modify transaction_id default 55 ;
modify transaction_id default 55
*
ERROR at line 2:
ORA-30674: identity column cannot have a default value
Create table as select does not inherit the IDENTITY property of a
column in the source table.
demo@ORA12C> create table transaction6
2 as
3 select *
4 from transaction2;
Table created.
demo@ORA12C> select table_name ,has_identity
2 from user_tables
3 where table_name ='TRANSACTION6';
TABLE_NAME HAS
--------------- ---
TRANSACTION6 NO
demo@ORA12C>
When the table is dropped, the system created sequences will be
dropped.
demo@ORA12C> select table_name ,has_identity,
2 (select sequence_name
3 from user_sequences s ,
4 user_objects o
5 where o.object_name = t.table_name
6 and s.sequence_name like
'%'||o.object_id) as sequence_name
7 from user_tables t
8 where has_identity = 'YES';
TABLE_NAME HAS
SEQUENCE_NAME
--------------- --- ---------------
TRANSACTION3 YES
ISEQ$$_87440
TRANSACTION2 YES
ISEQ$$_87438
TRANSACTION4 YES
ISEQ$$_87442
demo@ORA12C> drop table transaction2 purge;
Table dropped.
demo@ORA12C> drop table transaction3 purge;
Table dropped.
demo@ORA12C> drop table transaction4 purge;
Table dropped.
demo@ORA12C> select table_name ,has_identity,
2 (select sequence_name
3 from user_sequences s ,
4 user_objects o
5 where o.object_name = t.table_name
6 and s.sequence_name like
'%'||o.object_id) as sequence_name
7 from user_tables t
8 where has_identity = 'YES';
no rows selected
demo@ORA12C>
