In this article I’ll talk about identity column option, which is one of the new features that have come with Oracle DB 12c R1. I hope this will be a helpful article in terms of awareness.

It’s always been the major of our complaints that we cannot set Primary Key to rise automatically as built-in while creating a table. We solved this either by adding a manuel from sequence to the table during insert or by defining a trigger for our table and rising p.k column automatically with the help of a sequence during insert. In Oracle 12c R1 the infrastructure to make this operation automatic was developed and issued. Now, we’re in a position in which we can create numeric columns that can automatically rise.
Identity column type was developed in a way that will support three different features on the background. We can choose and use whichever suits us the best.

These features are;

1- GENERATED ALWAYS AS IDENTITY

2- GENERATED BY DEFAULT AS IDENTITY

3- GENERATED BY DEFAULT ON NULL AS IDENTITY

Before viewing the details of these features, there are certain restrictions for columns created in identity type. We definitely need to take into consideration the following situations while creating this kind of columns.

Only one identity column can exist in one table.

The identity column we’ll create must definitely be defined in the type of numeric data.

We can’t define default value for a column we created in identity type.

If there’s another identity column in a table we copied using create table as select, the identity feature of this column cannot be moved to the newly-created table.

The moment we define a column in identity type, NOT NULL constraint for this column will be defined as implicit.

Now, let’s analyze identity column kinds of different features by giving examples.

1- GENERATED ALWAYS AS IDENTITY: We can’t externally interfere with this identity column we created using this feature. Now, let’s see it with an example.

As we would reason from the running logs, we got an error by the system in the first insert we performed; because we sent external value to the identity column. However, the second phrase ran successfully and our identity column started by automatically rising since there was no such an interference in the second insert phrase.

2- GENERATED BY DEFAULT AS IDENTITY:With this feature, we can fill our identity column with an external value, yet we still can’t choose null. Now, let’s analyze this, too.

As it’s seen, the second phrase was inserted successfully. However, because the value we gave manually was the same as the first value which identity column automatically produced, the first values of the two records are the same. If the column we defined in identity type were the primary key, the second phrase would get an error for running over p.k constraint.

3- GENERATED BY DEFAULT ON NULL AS IDENTITY:If we create our identity column using this option, we can ensure that column value keeps rising automatically without any mistakes.

We observe that both of the insert phrases ran successfully. Now, let’s take a glance at the state of our table.

We analyzed how it can be used with three different features. Now, let’s mention what’s happening on the background.

The moment we created an identity column, it automatically creates a sequence for this column that will produce the automatic value.
Afterwards, it carries out the operation of producing value from this sequence with every new record, and writing it to the table. You can get at the sequence produced for our identity column by entering the data dictionary the query below.

select * from ALL_TAB_IDENTITY_COLS;

As you would understand from this dictionary query, we displayed that a sequence was created on the background. This might beg the question: Can we specialize the sequence’s running principle while we are creating the identity column? In other words, can we make the sequence advance by increasing by five? The answer is yes. Let’s demonstrate it in the example below:

As one can see, we can specialize the sequence which produces the identity column.
In this article, I analyzed deeply the identity column, which is one of the new characteristics that have come with 12c, and its features. I hope this has been a helpful article for your awareness.