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.
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.
CREATE TABLE [USER] ( [U_ID] [int] IDENTITY (1, 1) NOT NULL, [FNAME] [varchar] (100) NOT NULL, [LNAME] [varchar] (100) NOT NULL ) GO ALTER TABLE [USER] WITH NOCHECK ADD CONSTRAINT [PK_USER] PRIMARY KEY CLUSTERED ( [U_ID] ) GO
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:
CREATE TABLE USER ( U_ID number(10) not null; FNAME varchar2(100) not null; LNAME varchar2(100) not null ) ; ALTER TABLE USER ADD CONSTRAINT PK_USER PRIMARY KEY ( U_ID ) ; ...
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.
... CREATE SEQUENCE SEQ_USER_ID MINVALUE 1 MAXVALUE 999999999999999999 START WITH 1 INCREMENT BY 1 NOCACHE ; ...
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.
- 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.
- 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.
... CREATE OR REPLACE TRIGGER TRG_USER_INSERT BEFORE INSERT ON USER FOR EACH ROW BEGIN IF :new.U_ID IS NULL THEN SELECT SEQ_USER_ID.nextval INTO :new.U_ID FROM DUAL; END IF; END; / COMMIT;
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.
Is there any other way to insert in ID field of Oracle, other than calling trigger and explicitly invoking SEQ.nextVAL?
I am not aware of any other way that you can do this. If you figure out a way please do let me know, as although I understand the power and logic of using Sequences in Oracle and similar databases, I just love the simplicity of simple incremental Identity fields.