Let’s talk about a “developer” feature today. If you have a need to generate a column value [mostly primary key] based on a sequence, in pre-12c Oracle database we have to create a number data type column in the table, and use a sequence that is separately created. There is no restriction that the sequence can be used only to populate one table or column.
To comply with ANSI SQL [extension] Oracle 12c now includes the “INDENTITY” column. While defining the table [CREATE TABLE] or modifying the table [ALTER TABLE] you can now define the IDENTITY column with a “sequence generator” definition.
According to Oracle documentation, the syntax for this new option in CREATE TABLE is
GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ] AS IDENTITY [ ( identity_options ) ]
GENERATED keyword tells Oracle that this column value is generated.
ALWAYS is the default and specifies that the column value is never assigned and during INSERT/UDPATE statements, this column will always be evaluated to NULL – value will be populated by Oracle based on the “identity_options”.
BY DEFAULT specifies that the column value is generated by Oracle (similar to ALWAYS), but you can explicitly assign values to the column using INSERT/UPDATE statements. If you specify ON NULL with BY DEFAULT, generated value is assigned to the column only when the column value is evaluated to NULL during INSERT/UPDATE.
“identity_options” is basically the syntax for sequence generator – same as the CREATE SEQUENCE options.
There are some restrictions, please read the Oracle documentation link above. Notable one is that you can have only one IDENTITY column per table. IDENTITY column has a NOT NULL constraint automatically created.