Oracle DB Identity Columns

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.

1966595It’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.

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 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.

2ww

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.

 

CREATE TABLE tab_new_identity_col_table (
  tab_pk      NUMBER GENERATED BY DEFAULT 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 (1,'deneme',99,'dene12'); 

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

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 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.

fnl1

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.

fnl2We 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;

fnl3

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:

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'); 

fnl4 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.

REFERENCES

Advertisements

About ... from Emrah METE

Bilgisayar Mühendisi
This entry was posted in Oracle, Root and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s