Identity fields in Oracle and SQL Server

February 10, 2011|Other Coding2

While it may be relatively easy to create an incrementing and unique identifier inside a table in SQL Server, things get tricky with Oracle. In this article, we’ll see the differences between the two databases and offer a way of solving the problem.

Our example

In database development, most tables have an identity field that is usually the primary key, is unique and auto-increments by one with each new row. To illustrate the difference between the two databases, we will create a simple table made up of 3 fields: the first name, last name and a unique ID for a user. A screenshot from SQL Server shows how the finished table will look like:

The simple world of SQL Server

Even though, we can easily create the table with the SQL Server Enterprise Manager, let’s see how to do it with pure SQL code. Hopefully, this will help clarify the differences between the two databases.

As you can see, we can define the identity field inside the CREATE TABLE statement, and at the same time declare the identity seed (starting number) and increment.  In an ALTER statement, we add a primary key constraint to our table as well.  Overall, very simple.

In Oracle things get weird

Let’s first create the same table:

Right away we can see some differences in the syntax. A varchar data type in SQL Server translates to a varchar2 data type in Oracle, and an int (integer) to a number with a certain length. Also, the [ and ] brackets are not used in Oracle. But more important than that, is that we cannot define a unique identifier inside the CREATE TABLE statement. To do that we need a Sequence.

Sequences are Oracle’s way of creating unique identifiers.  Sequences have a name, a minimum and maximum value, and just like SQL Server, a seed and an increment value. There are some advanced options as well, such as caching a certain amount of values in memory to increase write speed – very handy for tables that have frequent inserts. Let’s create a sequence for our USER table.

When a new record is inserted into our table, we need to add the unique identifier along with it. To do this we must call the sequence and get the next value.  We must then add that number to our insert statement. There are 2 ways to do this.

1. Call the sequence separately, get the next value, and add it to the INSERT INTO statement of the rest of the fields. This takes the form of “select SEQ_USER_ID.nextval from dual;“, where SEQ_USER_ID is the name of a sequence.
2. Create a trigger that fires before the insert statement, which calls the sequence automatically, gets the next value and inserts it in the U_ID, provided nothing is passed for the U_ID.

The second method is a lot easier to maintain and develop with. Let’s do it this way then, and create the trigger.

The nextval method of a sequence fetches the next value in the sequence and selects it into, or inserts it, the proper field of our table.

We saw how to create a unique identifier in Oracle, as compared to SQL Server.  Initially, Oracle’s way may seem like trying to scratch your left ear by raising your right arm over your head… However, it is more powerful and flexible.  It’s possible for example, to use the same sequence for many tables. That way, you are guaranteed a unique identifier across all of them. One potential pitfall of this method, is that if someone passes a valid U_ID to the INSERT INTO statement, the trigger will not fire. The passed value would then simply be inserted into the U_ID – this could cause the U_IDs to be out of sync, and a future INSERT to fail when the U_ID happens to hit that same value.