Mimicking SQL Server identity column in Oracle

• Download script - 0.5 KB
• Download source code - 9.3 KB

Introduction

Every now and then a question is asked, how do I define an identity (auto-increment) column in Oracle like in SQL Server? There's no such concept in Oracle directly, but similar behavior can easily be created.

Unique numbering in Oracle

An identity definition in SQL Server is bound to a single column which is auto-numbered at insertion time. Typically the column is defined with an identity starting from 1 incremented by 1. After the column definition, SQL Server takes care of giving the row a 'unique' value in the identity column.

In Oracle, an automatically generated numerical value cannot be defined in table creation. Instead, Oracle has a special object type called sequence to generate unique numbers. One big difference is that the sequence isn't bound to any table or column, so it can be used wherever appropriate. So to achieve a similar behavior as in SQL Server, the value produced by a sequence must be inserted to a column of a row using code.

Let's create a small test table to use:

-----------------------------------------------
-- Create the table for testing
-----------------------------------------------
CREATE TABLE IdentityTest (
   Id   NUMBER        NOT NULL PRIMARY KEY,
   Text VARCHAR2(100) NOT NULL
);

The idea is to add rows to this table by specifying a value only for the text column. The ID column should get a value 'automatically'.

The next step is to create a sequence:

-----------------------------------------------
-- Create a sequence
-----------------------------------------------
CREATE SEQUENCE SeqIdentityTest
   START WITH 1
   INCREMENT BY 1
   NOCYCLE;

The above statement creates a new sequence object and the numbering starts from 1 incrementing always by 1. One important thing to notice is the NOCYCLE definition. This means that when the upper limit of a sequence is reached, it doesn't start giving numbers from the beginning. Instead, trying to get a new value from the sequence generates an error. This is the default behavior of a sequence and shouldn't be modified if the sequence is supposed to produce key values.

Now to combining the table column and the sequence. A very easy and invisible way is to create a trigger to the table:

-----------------------------------------------
-- Create a trigger to add a unique value to the id
-----------------------------------------------
CREATE OR REPLACE TRIGGER IdentityTest_Insert
BEFORE INSERT ON IdentityTest
FOR EACH ROW
BEGIN
   SELECT SeqIdentityTest.NEXTVAL
   INTO :NEW.Id
   FROM Dual;
END;
/

The trigger is fired separately for each row that is inserted into IdentityTest. The body of the trigger simply fetches a new value from the sequence and places the result in :NEW.Id. :NEW refers to the row as it's going to be inserted into the table after the trigger execution.

Since Oracle doesn't support a SELECT statement without a table to select from, dual is used. dual is a built-in table containing a single row so selecting from dual results always in one row.

Let's test what happens with Insert:

-----------------------------------------------
-- Test insert
-----------------------------------------------
INSERT INTO IdentityTest (Text) VALUES ('Test2');
SELECT * FROM IdentityTest;

The result is:

   ID TEXT
----- ----------
    1 Test2

As in SQL Server, sequence doesn't guarantee that every value the sequence has produced is actually found from the database. It only guarantees that the same value won't be supplied twice. For example, if a row is inserted to the IdentityTest table but the transaction is rolled back, the value sequence generated is gone.

How to know the generated value

It's nice that the ID column gets a value automatically, but what if the application needs to know what was the value given to the row? For example, if the table is a parent table and next the application needs to insert to a child table and use the primary key value from the parent table in the child table's foreign key column. It's necessary to get the value back to the calling application.

The included small demo program shows how the RETURNING clause can be used to get the value back to the client. It's well worth noticing that the RETURNING clause isn't supported with ADO.NET OleDbCommand with the msdaora provider. Because of this, ODP.NET is used in the demo application. ODP.NET can be found at Oracle Data Provider for .NET.

In order to use the application, define a proper connection string for the Oracle connection. The default connection string contains the already default port 1521, for instance XE. If the instance is correct, just add the proper user ID and password.

The code is as follows:

Oracle.DataAccess.Client.OracleConnection connection = new Oracle.DataAccess.Client.OracleConnection();
Oracle.DataAccess.Client.OracleCommand command = new Oracle.DataAccess.Client.OracleCommand();
Oracle.DataAccess.Client.OracleParameter parameter;
int rowsAffected;

connection.ConnectionString = this.ConnectionString.Text;
command.CommandText = "INSERT INTO IdentityTest (Text) VALUES (:text) RETURNING Id INTO :id";

parameter = new Oracle.DataAccess.Client.OracleParameter(":text", "Test 1");
command.Parameters.Add(parameter);

parameter = new Oracle.DataAccess.Client.OracleParameter();
parameter.ParameterName = ":id";
parameter.DbType = System.Data.DbType.Int64;
parameter.Direction = System.Data.ParameterDirection.ReturnValue;
command.Parameters.Add(parameter);

command.Connection = connection;

try {
   connection.Open();
   rowsAffected = command.ExecuteNonQuery();
   System.Windows.MessageBox.Show(string.Format("Added a row with id: {0}",
      command.Parameters[":id"].Value.ToString()),
      "Operation succeeded");
} catch (System.Exception exception) {
   System.Windows.MessageBox.Show(exception.Message,
      "Exception occurred",
      MessageBoxButton.OK,
      MessageBoxImage.Error);
} finally {
   connection.Dispose();
   command.Dispose();
}

The key thing in the code is the Insert statement. As in the first test, it contains a value only for the second column. This value is delivered to the database using a parameter. The Insert statement is defined to return the inserted value from Id to a second parameter :id. This is achieved by adding:

RETURNING Id INTO :id

to the end of the insert statement. The first parameter is a normal input parameter going from client to server. But the direction of the second parameter is defined as System.Data.ParameterDirection.ReturnValue so this means that the parameter value is filled during the call.

When the Insert is executed, the program shows the value for the ID field from the row that was inserted. Now the program can store this value and use it in subsequent calls to the database.

Replication

When replication is involved, automatically generated numbers are always an issue. SQL Server is quite strict when replicating tables with identity columns. Even though separate sequences can be used in two databases participating in replication in Oracle, the main problem still remains. How can we prevent duplicate values across databases?

One way is to give the sequences whole different number ranges in each database. The same solution that can be used with SQL Server. Depending on the situation, a more proper solution may be to create a composite key. This means that the key contains more than one column. For example, the first column could be a static value in the scope of a single database such as Site or Database ID, and the second column is the actual generated number. With this solution, the sequences may perfectly well produce same values but the integrity of the primary key isn't compromised even if rows are transferred from a database to another.

Since sequences are separate objects, a replicating database can actually also fetch the new value from a common sequence located in a single database. However, this also means that the databases are not independent anymore so this solution isn't typically used in replicating environments but in distributed databases.

Other mechanisms

Using a sequence isn't the only option. SQL Server has a NEWID() function to create a value for a uniqueidentifier column. Oracle has a similar (but not exactly the same) function called SYS_GUID(). In Oracle, this function produces a new globally unique RAW value. This function can be used similarly as the sequence in the example. The value of SYS_GUID() can be inserted using a trigger or by setting it as a default value for a primary key column. When default is used, no additional trigger has to be created. However, with defaults, there's always a risk that the client actually supplies the value so to be on the safe side, a trigger may have to be created. Getting the RAW value to the client follows the same rules as used in the example application.

When SYS_GUID() is used in replicating environments, a composite key isn't needed anymore since the value is globally unique. Each database can produce a new value and they won't overlap.

References

Some references for the article:

• CREATE TABLE
• CREATE SEQUENCE
• CREATE TRIGGER
• INSERT
• SYS_GUID

History

• January 8, 2012: Article created.