This page discusses identity columns which can be specified in the CREATE TABLE and ALTER TABLE statements. 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 clause:
CREATE TABLE transaction1 ( transaction_id NUMBER, holding_id NUMBER, transaction_type NUMBER, transaction_date DATE, credit_value NUMBER, debit_value NUMBER );
This definition creates the following table
SQL> DESC transaction1 Name Null? Type ----------------------------------------- -------- ------------------------ TRANSACTION_ID NUMBER HOLDING_ID NUMBER TRANSACTION_TYPE NUMBER TRANSACTION_DATE DATE CREDIT_VALUE NUMBER DEBIT_VALUE NUMBER
In its simplest form the IDENTITY clause can be specified as follows:
CREATE TABLE transaction2 ( transaction_id NUMBER GENERATED AS IDENTITY, holding_id NUMBER, transaction_type NUMBER, transaction_date DATE, credit_value NUMBER, debit_value NUMBER );
This definition creates the following table:
SQL> DESC transaction2 Name Null? Type ----------------------------------------- -------- ------------------------ TRANSACTION_ID NOT NULL NUMBER HOLDING_ID NUMBER TRANSACTION_TYPE NUMBER TRANSACTION_DATE DATE CREDIT_VALUE NUMBER DEBIT_VALUE NUMBER
Note that a NOT NULL constraint has been automatically created for the TRANSACTION_ID column
A sequence is created with a system-generated name in the format ISEQ$$_<objectID> where objectID is the object id of the table.
In the above example, the OBJECT_ID of the TRANSACTION table is 92210 and the system-generated name of the sequence is ISEQ$$_92210.
The automatically created sequence is visible in DBA_SEQUENCES. For example:
SELECT sequence_owner, sequence_name
FROM dba_sequences;
SEQUENCE_OWNER SEQUENCE_NAME
-------------- -------------
US01 ISEQ$$_92210
Note that the sequence is owned by the table owner (US01), not the SYS user.
By default the GENERATED AS IDENTITY clause implicitly includes 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 statements. For example the following statement:
INSERT INTO transaction2 (transaction_id,holding_id,transaction_type,transaction_date,credit_value) VALUES (50,100,1,SYSDATE,300);
will result in an error:
ORA-32795: cannot insert into a generated always identity column
Values can be generated automatically if no values are specified explicitly using the following syntax:
GENERATED BY DEFAULT AS IDENTITY
If you only wish values to be generated when a NULL value is supplied use:
GENERATED BY DEFAULT AS IDENTITY ON NULL
More sophisticated syntax is available. For example (from the documentation):
CREATE TABLE transaction3
(
transaction_id NUMBER
GENERATED BY DEFAULT AS IDENTITY (START WITH 100 INCREMENT BY 10),
holding_id NUMBER,
transaction_type NUMBER,
transaction_date DATE,
credit_value NUMBER,
debit_value NUMBER
);
In the above example, a starting value and increment value have been specified for the sequence. Interestingly the increment value stored in the INCREMENT$ column of the SEQ$ table in the data dictionary. However the starting value does not appear to be stored in this table. When the first row is inserted into the table, the TRANSACTION_ID column will be set to the value of 100. So it works, but I don't fully understand why it works.
We can check which tables have identity columns in the DBA_TABLES
SELECT table_name,has_identity FROM dba_tables WHERE owner = 'US01' AND table_name LIKE 'TRANSACTION%'; TABLE_NAME HAS_IDENTITY ------------------------------ ------------ TRANSACTION1 NO TRANSACTION2 YES TRANSACTION3 YES
When the identity clause is specified, a couple of extra bits are set in the PROPERTY column of the TAB$ table.
SELECT o.name AS name, TO_CHAR (t.property,'XXXXXXXXXXXXXXXXXX') AS property FROM sys.tab$ t,sys.obj$ o WHERE t.obj# = o.obj# AND o.owner# = ( SELECT user# FROM sys.user$ WHERE name = 'US01' ) AND o.name LIKE 'TRANSACTION%'; NAME PROPERTY ------------ --------------- TRANSACTION1 420000000 TRANSACTION2 400000420000000 TRANSACTION3 400000420000000
According to $ORACLE_HOME/rdbms/admin/dcore.bsq the flag values are:
0x20000000 = pdml itl invariant 0x400000000 = delayed segment creation 0x400000000000000 = has identity column
The delayed segment creation flag is unset when a segment is created for the table i.e. when the first row is inserted.
The identity column property shown above is used to populate the HAS_IDENTITY column in the DBA_TABLES view.
The PROPERTY column in SYS.COL$ is also set for the identity clause. The following query lists the value of the PROPERTY column in COL$ for the TRANSACTION_ID column in each of the three tables:
SELECT o.name AS table_name, c.name AS column_name, TO_CHAR (c.property,'XXXXXXXXXXXXXXXXXX') AS property, c.default$ AS default_value FROM sys.col$ c,sys.tab$ t,sys.obj$ o WHERE c.obj# = t.obj# AND t.obj# = o.obj# AND o.owner# = ( SELECT user# FROM sys.user$ WHERE name = 'US01' ) AND o.name LIKE 'TRANSACTION%' AND c.intcol# = 1;
Results are:
TABLE_NAME COLUMN_NAME PROPERTY DEFAULT_VALUE ---------- ------------- ---------- ----------------------------- TRANSACTION1 TRANSACTION_ID 0 TRANSACTION2 TRANSACTION_ID 2800000000 "US01"."ISEQ$$_92210".nextval TRANSACTION3 TRANSACTION_ID 4800000000 "US01"."ISEQ$$_92212".nextval
According to $ORACLE_HOME/rdbms/admin/dcore.bsq the flag values are:
0x002000000000 = generated always identity column 0x004000000000 = generated by default identity col
Under the covers, therefore the identity clause uses the default column value functionality.
The ALTER TABLE statement supports the identity clause, but only to modify the attributes of EXISTING identity columns. In other words you cannot alter an existing non-identity column to become an identity column. If you try to do this the following error will be returned:
ORA-30673: column to be modified is not an identity column
In this example the table is created with an identity column. The BY DEFAULT ON NULL clause ensures that initially the identity column will only be populated automatically if no value is supplied for the column:
CREATE TABLE transaction4 ( transaction_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY, holding_id NUMBER, transaction_type NUMBER, transaction_date DATE, credit_value NUMBER, debit_value NUMBER );
Assume the following statements are executed. Note that a value for the TRANSACTION_ID column is specified explicitly.
INSERT INTO transaction4 VALUES (1001,10,1,SYSDATE,200,0); INSERT INTO transaction4 VALUES (1002,11,2,SYSDATE,0,100); INSERT INTO transaction4 VALUES (1003,21,1,SYSDATE,0,200); INSERT INTO transaction4 VALUES (1004,22,1,SYSDATE,300,0); COMMIT;
The highest value for this column can be verified using:
SELECT MAX(transaction_id) FROM transaction4;
MAX(TRANSACTION_ID)
-------------------
1004
If we now decide that we want this value to be populated automatically we can issue the following ALTER TABLE statement:
ALTER TABLE transaction4 MODIFY transaction_id GENERATED ALWAYS AS IDENTITY (START WITH LIMIT VALUE);
The START WITH LIMIT VALUE clause can only be specified with an ALTER TABLE statement (and by implication against an existing identity column). When this clause is specified, the table will be scanned for the highest value in the TRANSACTION_ID column and the sequence will commence at this value + 1. In this case the highest value is 1004 so the next sequence number will be 1005.
For example, we can now execute the following statements which do not specify a value for the TRANSACTION_ID column:
INSERT INTO transaction4 (holding_id,transaction_type,transaction_date,credit_value,debit_value) VALUES (31,1,SYSDATE,150,0); INSERT INTO transaction4 (holding_id,transaction_type,transaction_date,credit_value,debit_value) VALUES (32,2,SYSDATE,0,250); COMMIT;
The value for TRANSACTION_ID is generated automatically.
We can verify the highest value for this column again:
SELECT MAX(transaction_id) FROM transaction4;
MAX(TRANSACTION_ID)
-------------------
1006
Whilst we cannot change a non-identity column to an identity column, we can convert an identity column to a non-identity column:
For example if we create the following table:
CREATE TABLE transaction5
(
transaction_id NUMBER GENERATED AS IDENTITY,
holding_id NUMBER,
transaction_type NUMBER,
transaction_date DATE,
credit_value NUMBER,
debit_value NUMBER
);
SELECT has_identity
FROM dba_tables
WHERE owner = 'US01'
AND table_name = 'TRANSACTION5';
HAS_IDENTITY
------------
YES
We can use the ALTER TABLE statement to drop the identity column metadata, though not the column itself:
ALTER TABLE t104 MODIFY transaction_id DROP IDENTITY;
SELECT has_identity
FROM dba_tables
WHERE owner = 'US01'
AND table_name = 'TRANSACTION5';
HAS_IDENTITY
------------
NO
As you would expect there are a few restrictions on the use of identity columns.
One important point to note is that when the table is dropped; for example:
DROP TABLE t1;
The system-generated sequence is not dropped. As the sequence name is based on the object number of the table, which cannot be reused, this is not particularly significant unless the table will be dropped and recreated on a regular basis.
There are some subtle differences between the identity clause and the new feature that allows a column default clause to include the NEXTVAL or CURRVAL pseudo-columns. I have called the latter feature implicit sequences though this is not the official name.
With implicit sequences, the sequence must already exist and you must have the correct permissions to increment it; with the identity clause, the sequence is system-generated when the table is created or altered.
When a table that uses implicit sequences is dropped and recreated, the sequence will be unaffected and the next insertion into the table will use the next value allocated from the sequence.
When a table that uses the identity clause is dropped and recreated, the sequence will still be unaffected. However, a new sequence will be created when the table is recreated and insertions will use values from the new sequence.
With implicit sequences, multiple tables can share a single sequence value. With the identity clause, each table has a dedicated sequence and therefore sharing is not possible.
Implicit sequences can be introduced for existing tables; the identity clause cannot be used to alter an existing table unless the column on which it acts was originally created as an identity column.