PL/SQL IDENTITY Column

The Oracle PL/SQL IDENTITY column is a column whose values are automatically generated by the database system, typically used for creating surrogate primary keys. This feature simplifies the process of managing unique identifiers for records in a table.

An identity column is a special type of column in Oracle that automatically generates unique values for each row. This is similar to the AUTO_INCREMENT column in MySQL or the IDENTITY column in SQL Server. Identity columns are very useful for surrogate primary key columns, which are columns that are used to uniquely identify each row in a table, but do not have any inherent meaning.

Creating an Identity Column

An identity column can be created when you create a table, or change an existing table to add an identity column using ALTER TABLE statement. In either case, choose one of the IDENTITY statements described below.

GENERATED ALWAYS AS IDENTITY
GENERATED BY DEFAULT ON NULL AS IDENTITY

The GENERATED ALWAYS AS IDENTITY and GENERATED BY DEFAULT ON NULL AS IDENTITY clauses are used to define identity columns in a table.

GENERATED ALWAYS AS IDENTITY

When you use the GENERATED ALWAYS AS IDENTITY clause, Oracle will always generate a value for the identity column, regardless of whether a value is explicitly provided during an INSERT statement or not. Here’s an example of how you might use this clause in a table creation statement:

CREATE TABLE example_table (
    id_column NUMBER GENERATED ALWAYS AS IDENTITY,
    other_column VARCHAR2(50)
);

In this example, the id_column is an identity column, and Oracle will always generate a unique value for it, even if a value is specified during an INSERT operation.

GENERATED BY DEFAULT ON NULL AS IDENTITY

On the other hand, when you use the GENERATED BY DEFAULT ON NULL AS IDENTITY clause, Oracle will generate a value for the identity column only if a NULL value is explicitly provided during an INSERT statement.

CREATE TABLE example_table (
    id_column NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    other_column VARCHAR2(50)
);

In this case, if you insert a row without specifying a value for id_column or if you explicitly insert a NULL value for id_column, Oracle will generate a unique identifier for that column.

INSERT INTO example_table (other_column) 
VALUES ('Some value');

In the above example, Oracle will automatically generate a unique value for id_column since it was not provided in the INSERT statement.

Using identity columns simplifies the management of primary key values, especially when dealing with auto-incrementing values, as Oracle takes care of the uniqueness and generation of these values. This can be particularly useful in scenarios where you need a straightforward and reliable way to manage unique identifiers in your database tables.