Oracle 12c IDENTITY Columns & Default SEQUENCES

 

I will try to explain the identity feature, which is my favorite innovation in Oracle 12c.

MS SQL and MYSQL users know the Identitiy feature. It is now also available in Oracle databases.

We used SEQUENCE when this feature wasn’t available. While adding records to the table, we would take the new value of this SEQUENCE. Trigger can also be created for this operation.

Oracle 12c offers 2 options for this process: the IDENTITY column and the SEQUENCE to be used as the default value of the column.

 

Identitiy Column:

The IDENTITY column is new on Oracle, but has been available for many years in other databases. Actually IDENTITY columns creates a SEQUENCE in the background  . But this is done automatically. The IDENTITY value is reset when the table is dropped or re-created.

Oracle databases have SEQUENCE for many years. The IDENTITY column provides the use of a SEQUENCE as part of the column definition.

 

Usage:
• Specified as “GENERATED AS IDENTITY”.
• The default value starts from 1 and increases 1 by 1.
• Value can be assigned with START WITH and INCREMENT BY and the increment value can be set.
• The IDENTITY column is reset if the table is dropped or re-created.

 

Example 1:
In this example, the value of the column with IDENTITY starts from 1 and increases 1 by 1.

 

Example 2:
In this example, the initial value and increment value of the IDENTITY column are specified.

 

A column with DEFAULT value is SEQUENCE:

In Oracle 12c, we can assign the default value to column in several ways:

  • A default value can be assigned when column value is added as NULL.
  • The default value of the column is given with a SEQUENCE (.nextval or .currval).

 

Example:

In this example, the default value of the id column is assigned as a sequence.

When the value of the surname column is NULL, it is set to “unidentified”. This means that we dont need a trigger to assign an automatic value to the column.

First we create a sequence:

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *