Alternate To Identity in Oracle

Hello! Friends, we understand that identity is a property in SQL Server for creating an auto-increment value for a column. An identity column has a name, initial seed and step. When a row is inserted into a table, the column will get the value of the current seed incremented by the step. If we use an identity property for a column then a user can't explicitly insert data into that column. The data in an identity column is inserted by the database system.

The following is the syntax for the Identity property:

CREATE TABLE TABLE_NAME
(
   COLUMN_NAME DATATYPE IDENTITY(1,1)
)

Using the preceding syntax we can easily create an Identity column in SQL Server. AUTO_INCREMENT is used in My SQL to create an identity column. AUTOINCREMENT is used in Access to create an identity column.

But Oracle doesn't provide any such type of keyword to create an identity column. If we use any of the preceding keywords to create an identity column then the Oracle Database will throw an error.

The following is an example:



If we execute the preceding query then the Oracle database system will throw an error like “missing right parenthesis”. This indicates that the IDENTITY method cannot be used to create an auto increment field.

We use another mechanism in Oracle databases to create an identity field for a table. We use a trigger and a Sequence object for this. If we want to insert an auto increment value into a column then we should first create a trigger and a Sequence Object . This trigger will be invoked when we insert data into the table. Then the Trigger will fetch the value from the Sequence Object and insert it into the identity column.

Let us see an example.

First of all we create a table.



Assume in the preceding table we want to create an Auto Increment method for the Emp_Id column.

Create a Sequence Object

Sequence is a type of user-defined object that generates a sequence of numeric values. We can use a sequence object for T-SQL queries. A sequence object starts with a specific value. The value of a sequence object can be increased or decreased at a specific interval. The Sequence object has an option for a minimum and maximum value. Applications refer to a sequence object to retrieve its next value. The relationship between sequences and tables is controlled by the application. Sequences, unlike identity columns, are not associated with specific tables.

Syntax

CREATE SEQUENCE [schema_name . ] sequence_name
[ START WITH <constant> ]
[ INCREMENT BY <constant> ]
[ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
[ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ <constant> ] } | { NO CACHE } ]
[ ; ]

Let us create a Sequence Object for our table.



Create a Trigger for the Sequence Object

Now we create a Trigger for the Sequence Object. Triggers are stored programs that are automatically executed or fired when some events occur. It is a database object that is bound to a table and is executed automatically. We cannot explicitly call a trigger. Triggers provide data integrity and are used to access and check data before and after modification using a DDL or DML query.

Let us create a trigger:



We create a trigger that will be invoked before an insert of data into the table. In this trigger we fetch the next serial number from the Sequence Object and insert this value into the Emp_ID field of EMPLOYEE TABLE. Here Dual is a type of table that contains a special one-row, one-column table present by default in Oracle and other database installations. In Oracle, the table has a single VARCHAR2(1) column called DUMMY that has a value of "X".

When we insert data into the table this trigger will be invoked. This trigger fetches the next value from the sequence object and inserts it into the Emp_Id field of the table. Here we use :new.Emp_Id to declare the Emp_Id field of the table.

First we insert some data into the table.



Now retrieve the data from the table.



We can see that in this table the value of the Emp_ID column is automatically inserted by the trigger.

Note: If we want to insert an auto increment value into a column then it requires some more efforts in the Oracle Database System than other database systems (in other words SQL Server, My SQL or Access). But this approach has some advantages over other database systems like:

1. We can reset the value of the sequence object.
   
   
2. We can assign a maximum or minimum value to a sequence object.
   
   
3. A Sequence object can be defined as Cycle. Cycle indicates that the sequence continues to generate values after reaching either its maximum or minimum value.
   

We can also use this same approach in other database systems.