Introduction to Oracle identity column
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.