Identity Columns

We’ve previously looked at the use of sequence NEXTVAL as a column default in Oracle 12c and the ability to use column defaults when an inserted value is NULL. Oracle has another change in 12c to help out in the area of column default; identity columns.

Developers coming to Oracle from other database platforms are sometimes perplexed about the need to create sequence objects to populate simple integer primary key columns. The complaint typically is one of “Why doesn’t Oracle just do this for me?” and identity columns are directly aimed at this complaint.

In it’s simplest form, an identity column is declared as:

CREATE TABLE test_table
   (id      NUMBER(12) GENERATED AS IDENTITY
   ,padding CHAR(30) NOT NULL)

Oracle will automatically add a NOT NULL constraint to the identity column. No unique index or primary key constraint is added though so you will need to define those yourself, assuming the column will be used as a table key.

Behind the scenes, Oracle creates a sequence and will draw from that sequence whenever an insert into the table is done, via a default on the column, as illustrated below:

SQL>SELECT sequence_name
  2  ,      min_value
  3  ,      max_value
  4  ,      cache_size
  5  FROM   user_sequences
  6  /

no rows selected

SQL>CREATE TABLE test_table
  2     (id      NUMBER(12) GENERATED AS IDENTITY
  3     ,padding CHAR(30) NOT NULL)
  4  /

Table created.

SQL>INSERT INTO test_table (padding)
  2  VALUES ('x')
  3  /

1 row created.

SQL>SELECT *
  2  FROM   test_table
  3  /

        ID PADDING
---------- ----------
         1 x

SQL>SELECT sequence_name
  2  ,      min_value
  3  ,      max_value
  4  ,      cache_size
  5  FROM   user_sequences
  6  /

SEQUENCE_NAME         MIN_VALUE  MAX_VALUE CACHE_SIZE
-------------------- ---------- ---------- ----------
ISEQ$$_91711                  1 1.0000E+28         20

SQL>SELECT column_name
  2  ,      data_default
  3  FROM   user_tab_columns
  4  WHERE  table_name = 'TEST_TABLE'
  5  AND    column_name = 'ID'
  6  /

COLUMN_NAME     DATA_DEFAULT
--------------- -----------------------------------
ID              "DEVELOPER"."ISEQ$$_91711".nextval

Like any normal sequence, the automatically created sequence can be drawn from explicitly, although this is probably not recommended:

SQL>SELECT ISEQ$$_91711.NEXTVAL FROM dual
  2  /

   NEXTVAL
----------
         2

Sequence properties can be explicitly specified when creating identity columns:

SQL>CREATE TABLE test_table
  2     (id      NUMBER(12) GENERATED AS IDENTITY
  3                         (CACHE 1000
  4                          MINVALUE 1000
  5                          MAXVALUE 999999999999)
  6     ,padding CHAR(30) NOT NULL)
  7  /

Table created.

SQL>SELECT sequence_name
  2  ,      min_value
  3  ,      max_value
  4  ,      cache_size
  5  FROM   user_sequences
  6  /

SEQUENCE_NAME         MIN_VALUE  MAX_VALUE CACHE_SIZE
-------------------- ---------- ---------- ----------
ISEQ$$_91725               1000 1.0000E+12       1000

Unlike a traditional column with a default, the identify column cannot be directly inserted into:

SQL>INSERT INTO test_table (id, padding)
  2  VALUES (999, 'x')
  3  /
INSERT INTO test_table (id, padding)
                        *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

However, the column can be altered to replace the “ALWAYS GENERATED” clause with the “BY DEFAULT”. In this case the sequence will behave just like any other sequence allocated column default:

SQL>ALTER TABLE test_table
  2     MODIFY id GENERATED BY DEFAULT AS IDENTITY
  3  /

Table altered.

SQL>INSERT INTO test_table (id, padding)
  2  VALUES (999, 'x')
  3  /

1 row created.

Finally, if the table is dropped then the sequence used for the identity column is also dropped:

SQL>DROP TABLE test_table PURGE
  2  /

Table dropped.

SQL>SELECT sequence_name
  2  ,      min_value
  3  ,      max_value
  4  ,      cache_size
  5  FROM   user_sequences
  6  /

no rows selected

Advertisement