Oracle Identity Column

Summary: In this tutorial, you will learn how to use the Oracle identity column to define an automatically generated numeric column for a table.

Introduction to Oracle Identity column #

Oracle 12c introduced a new way that allows you to define an identity column for a table, which is similar to the AUTO_INCREMENT column in MySQL or IDENTITY column in SQL Server.

The identity column is handy for the surrogate primary key column. When you insert a new row into the identity column, Oracle automatically generates and inserts a sequential value.

A surrogate primary key is a system-generated unique identifier that is used as the primary key of a table instead of natural keys such as product code, email, or social security number.

To define an identity column, you use the identity clause:

GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ ( identity_options ) ]Code language: SQL (Structured Query Language) (sql)

First, the GENERATED keyword is mandatory.

Second, provide an option to generate identity values:

GENERATED ALWAYS: Oracle always generates a value for the identity column. You’ll encounter an error if you attempt to insert a value into the identity column.
GENERATED BY DEFAULT: Oracle generates a value for the identity column if you don’t provide any value. If you provide a value, Oracle will insert that value into the identity column. For this option, Oracle will issue an error if you insert NULL into the identity column.
GENERATED BY DEFAULT ON NULL: Oracle generates a value for the identity column if you provide NULL or no value.

Third, specify one or more options for the identity column:

START WITH initial_value controls the initial value to use for the identity column. The default initial value is 1.
INCREMENT BY internval_value defines the interval between generated values. By default, the interval value is 1.
CACHE defines a number of values that Oracle should generate beforehand to improve the performance. You use this option for the column that has a high number of inserts.

GENERATED ALWAYS example #

First, create a table called messages that consists of an identity column as the primary key:

CREATE TABLE messages(
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    description VARCHAR2(100) NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Second, insert a new row into the messages table:

INSERT INTO messages(description)
VALUES('Oracle identity column demo with GENERATED ALWAYS');Code language: SQL (Structured Query Language) (sql)

Since we did not specify a value for the id column, Oracle automatically generated a sequential value starting from 1.

Third, retrieve data from the messages table:

SELECT * FROM messages;Code language: SQL (Structured Query Language) (sql)

Finally, attempt to insert a value into the id identity column:

INSERT INTO messages(id, description)
VALUES(2, 'Oracle identity column example with GENERATED ALWAYS ');Code language: SQL (Structured Query Language) (sql)

Oracle issued an error:

SQL Error: ORA-32795: cannot insert into a generated always identity columnCode language: SQL (Structured Query Language) (sql)

Since we defined the id column as GENERATED ALWAYS, it could not accept any provided value.

GENERATED BY DEFAULT example #

Let’s change the id column to GENERATED BY DEFAULT.

First, drop the messages table:

DROP TABLE messages;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, create the messages table:

CREATE TABLE messages(
    id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    description VARCHAR2(100) NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Third, insert a new row into the messages table:

INSERT INTO messages(description)
VALUES('Oracle identity column demo with GENERATED BY DEFAULT');Code language: SQL (Structured Query Language) (sql)

It worked as expected.

Fourth, insert a new row into the messages table with a value for the id column:

INSERT INTO messages(id, description)
VALUES(2, 'Oracle identity column example with GENERATED BY DEFAULT');Code language: SQL (Structured Query Language) (sql)

In this example, Oracle inserted the provided value into the table.

Fifth, retrieve data from the messages table:

SELECT * FROM messages;Code language: SQL (Structured Query Language) (sql)

Output:

Finally, attempt to insert a null value into the id column:

INSERT INTO messages(id,description)
VALUES(NULL, 'Oracle identity column demo with GENERATED BY DEFAULT, NULL value');Code language: SQL (Structured Query Language) (sql)

Oracle issued an error:

SQL Error: ORA-01400: cannot insert NULL into ("OT"."MESSAGES"."ID")Code language: SQL (Structured Query Language) (sql)

GENERATED BY DEFAULT ON NULL example #

First, change the id column of the messages table to GENERATED BY DEFAULT ON NULL:

DROP TABLE messages;

CREATE TABLE messages(
    id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    description VARCHAR2(100) NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Second, insert a new row into the messages table, but provide no value for the id column, Oracle will automatically generate a value for insert:

INSERT INTO messages(description)
VALUES('Oracle identity column demo with no value');Code language: SQL (Structured Query Language) (sql)

Third, insert NULL into the id column:

INSERT INTO messages(description)
VALUES('Oracle identity column demo with null');Code language: SQL (Structured Query Language) (sql)

Since we defined the id column as GENERATED BY DEFAULT ON NULL, Oracle generates a sequential value and uses it for insert.

Finally, retrieve data from the messages table:

SELECT * FROM messages;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

START WITH option example #

First, recreate the messages table with the id column defined as an identity column with the initial value starting from 100:

DROP TABLE messages;

CREATE TABLE messages (
    id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 100 PRIMARY KEY,
    description VARCHAR2(100) not null
  );Code language: SQL (Structured Query Language) (sql)

Second, insert a row into the messages table:

INSERT INTO messages (description)
VALUES('Oracle identity column demo with START WITH option');Code language: SQL (Structured Query Language) (sql)

Third, query data from the messages table:

SELECT * FROM messages;Code language: SQL (Structured Query Language) (sql)

The output indicates that the initial value of the id column is 100 as specified in the identity clause.

INCREMENT BY option example #

First, change the id column of the messages table that includes both START WITH and INCREMENT BY options.

DROP TABLE messages;

CREATE TABLE messages(
    id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 10 INCREMENT BY 10 primary key,
    description VARCHAR2(100) not null
);Code language: SQL (Structured Query Language) (sql)

Second, insert two rows into the messages table:

INSERT INTO messages(description)
VALUES('Oracle identity column demo 1 with INCREMENT BY option');

INSERT INTO messages(description)
VALUES('Oracle identity column demo 2 with INCREMENT BY option');Code language: SQL (Structured Query Language) (sql)

Third, query data from the messages table to verify the inserts:

SELECT * FROM messages;Code language: SQL (Structured Query Language) (sql)

The output shows:

The first row has the id value 10.
The second row has the id value 20.

These values align with the id column definition, which specifies that it should start at 10 and increment by 10 for each new row.

Oracle identity column restrictions #

The identity columns are subject to the following restrictions:

Each table has a maximum of one identity column.
The data type of the identity column must be a numeric data type. You cannot use a user-defined data type with the identity clause.
When you create a new table with the CREATE TABLE AS SELECT statement, Oracle does not transfer the identity column to the new table.
You cannot define an identity column with a DEFAULT constraint.
The encryption algorithm for encrypted identity columns can be inferred; therefore, you should use a strong encryption algorithm.
The inline constraint of the identity column must not conflict with the NOT NULL and NOT DEFERRABLE constraint stated by the identity clause.

Summary #

Use the Oracle identity column to define an automatically generated numeric column for a table.

Was this tutorial helpful?