Oracle 12c - Identity Columns

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.

Creating Identity Columns

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.

Altering Identity Columns

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

Dropping Identity Columns

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

Restrictions

As you would expect there are a few restrictions on the use of identity columns.

Dropping Tables with 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.

Differences between Identity Columns and Column Default Clauses

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.