Defining Identity Columns in Oracle

It’s always been a major complaint that we cannot set Primary Key to rise automatically as built-in while creating a table. We solved this either by adding a manual from the sequence to the table during insert or by defining a trigger for our table and rising the 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 in 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 these kinds 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 the default value for a column we created in the 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.

CREATE TABLE tab_new_identity_col_table (
  tab_pk      NUMBER GENERATED ALWAYS AS IDENTITY,
  tab_name    VARCHAR2(50),
  tab_prop    NUMBER,
  tab_add     VARCHAR2(50)  
);


insert into tab_new_identity_col_table(tab_pk,tab_name,tab_prop,tab_add) values (10,'deneme',99,'dene12'); 

insert into tab_new_identity_col_table(tab_name,tab_prop,tab_add) values ('deneme',99,'dene12'); 

Now, after having run the block of code above, let’s view the running logs.

Table TAB_NEW_IDENTITY_COL_TABLE created.

Error starting at line : 9 in command -
insert into tab_new_identity_col_table(tab_pk,tab_name,tab_prop,tab_add) values (10,'deneme',99,'dene12')
Error at Command Line : 9 Column : 40
Error report -
SQL Error: ORA-32795: cannot insert into a generated always identity column
32795.0000 -  "cannot insert into a generated always identity column"
*Cause:    An attempt was made to insert a value into an identity column
           created with GENERATED ALWAYS keywords.
*Action:   A generated always identity column cannot be directly inserted.
           Instead, the associated sequence generator must provide the value.

1 row inserted

As we would reason from the running logs, we got an error by the system in the first insert we performed; because we sent the 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.

Now that we ran the block of code above, let’s view the running logs.

Table TAB_NEW_IDENTITY_COL_TABLE created.


1 row inserted.


1 row inserted.

As it’s seen, the second phrase was inserted successfully. However, because the value we gave manually was the same as the first value that the 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 the 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.

CREATE TABLE tab_new_identity_col_table (
  tab_pk      NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
  tab_name    VARCHAR2(50),
  tab_prop    NUMBER,
  tab_add     VARCHAR2(50)  
);


insert into tab_new_identity_col_table(tab_pk,tab_name,tab_prop,tab_add) values (null,'deneme',99,'dene12'); 

insert into tab_new_identity_col_table(tab_name,tab_prop,tab_add) values ('deneme',99,'dene12'); 

Since we ran the block of code, let’s now view the running logs.

Table TAB_NEW_IDENTITY_COL_TABLE created.


1 row inserted.


1 row inserted.

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 discuss what’s happening in the background.

The moment we created an identity column, it automatically creates a sequence for this column that will produce the automatic value. Afterward, 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 in 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:

CREATE TABLE tab_new_identity_col_table (
  tab_pk      NUMBER GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 5),
  tab_name    VARCHAR2(50),
  tab_prop    NUMBER,
  tab_add     VARCHAR2(50)  
);


insert into tab_new_identity_col_table(tab_name,tab_prop,tab_add) values ('deneme',99,'dene12'); 

insert into tab_new_identity_col_table(tab_name,tab_prop,tab_add) values ('deneme',99,'dene12'); 

insert into tab_new_identity_col_table(tab_name,tab_prop,tab_add) values ('deneme',99,'dene12'); 

As one can see, we can specialize the sequence tgat produces the identity column.
In this article, I analyzed 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.