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 ) ]
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.
