How To Create Auto Increment Column In Oracle 11g

Summary: in this tutorial, you will learn how to use the Oracle identity column to easily define an automatic 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 very useful for the surrogate primary key column. When you insert a new row into the identity column, Oracle auto-generates and insert a sequential value into the column.

To define an identity column, you use the identity clause as shown below:

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

First, the GENERATED keyword is mandatory.

Second, you can specify an option to generate identity values:

GENERATED ALWAYS: Oracle always generates a value for the identity column. Attempt to insert a value into the identity column will cause an error.
GENERATED BY DEFAULT: Oracle generates a value for the identity column if you provide no 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 a NULL value into the identity column.
GENERATED BY DEFAULT ON NULL: Oracle generates a value for the identity column if you provide a NULL value or no value at all.

Third, you can have a number of 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.

Oracle identity column examples

Let's take some examples of using the Oracle identity columns.

A) `GENERATED ALWAYS` example

The following statement creates a table named identity_demo that consists of an identity column:

CREATE TABLE identity_demo ( id NUMBER GENERATED ALWAYS AS IDENTITY, description VARCHAR2(100) NOT NULL );

Code language: SQL (Structured Query Language) ( sql )

The following statement inserts a new row into the identity_demo table:

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

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

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

Oracle Identity Column - GENERATED ALWAYS example

The following statement attempts to insert a value into the id identity column:

                                          INSERT                INTO                identity_demo(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 column

Code language: SQL (Structured Query Language) ( sql )

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

B) `GENERATED BY DEFAULT` example

Let's change the id column to GENERATED BY DEFAULT:

DROP TABLE identity_demo; CREATE TABLE identity_demo ( id NUMBER GENERATED BY DEFAULT AS IDENTITY, description VARCHAR2(100) not null );

Code language: SQL (Structured Query Language) ( sql )

The following statement inserts a new row into the identity_demo table:

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

Oracle Identity Column - GENERATED BY DEFAULT example 1

It worked as expected.

The following statement inserts a new row into the identity_demo table with a provided value for the id column:

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

In this example, Oracle used the provided value and inserted it to the table.

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

Oracle Identity Column - GENERATED BY DEFAULT example 2

The following example attempts to insert a null value into the id column:

                                          INSERT                INTO                identity_demo(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"."IDENTITY_DEMO"."ID")

Code language: SQL (Structured Query Language) ( sql )

C) `GENERATED BY DEFAULT ON NULL` example

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

DROP TABLE identity_demo; CREATE TABLE identity_demo ( id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY, description VARCHAR2(100) not null );

Code language: SQL (Structured Query Language) ( sql )

The following statement provides no value for the id column, Oracle will automatically generate a value for insert:

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

The following statement inserts a NULL value into the id column because the id column has been defined as GENERATED BY DEFAULT ON NULL, Oracle generates a sequential value and uses it for insert:

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

Oracle Identity Column - GENERATED BY DEFAULT ON NULL example

D) `START WITH` option example

First, recreates the identity_demo table whose the id column is defined as identity column with the initial value starts from 100:

DROP TABLE identity_demo; CREATE TABLE identity_demo ( id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 100, description VARCHAR2(100) not null );

Code language: SQL (Structured Query Language) ( sql )

Second, insert a row into to the identity_demo table:

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

Third, query data from the identity_demo table:

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

Oracle Identity Column - START WITH option example

As you can see, the initial value of the id column is 100 as specified in identity clause.

E) `INCREMENT BY` option example

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

DROP TABLE identity_demo; CREATE TABLE identity_demo ( id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 10 INCREMENT BY 10, description VARCHAR2(100) not null );

Code language: SQL (Structured Query Language) ( sql )

Second, insert two rows into the identity_demo table:

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

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

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

Oracle Identity Column - INCREMENT BY option example

As you can see, the first row has the id value 10. The second row has the id value 20. This is what we defined for the id column that should start with 10 and increase by 10 for the new row.

Oracle identity column restrictions

The identity columns are subject to the following restrictions:

Each table has one and only one identity column.
The data type of the identity column must be a numeric data type. the user-defined data type is not allowed to use with the identity clause.
The identity column is not inherited by the CREATE TABLE AS SELECTstatement.
The identity column cannot have another 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.

In this tutorial, you have learned how to use the Oracle identity column that allows you easily define an automatic generated numeric column for a table.

Was this tutorial helpful?