Oracle: How to Create an Auto Increment Field Using Sequence

In Oracle, you can create an auto increment field using ‘sequence’ database object that can be assigned as primary keys. Using Oracle ‘sequence’ object, you can generate new values for a column. An Oracle sequence is an object like a table or a stored procedure. Examples with walkthrough explanations are provided.

Create a sequence.

Syntax:

Create sequence sequence_name
start with value
increment by value
minvalue value
maxvalue value;

Let’s walk through an example.

First, let’s create an emp table with primary key constraint on emp_id column.

SQL> create table emp (
emp_id number(10),
fname varchar2(25),
lname varchar2(25),
constraint pk_emp_id PRIMARY KEY(emp_id)
);

Now let’s create a sequence.

SQL> Create sequence emp_sequence
start with 1
increment by 1
minvalue 1
maxvalue 10000;

Now we have created a sequence object named emp_sequence with starting value as 1 and incrementing by 1 from 1 (minvalue) to 10000 (maxvalue).

Now let’s insert the values into emp table.

SQL> insert into emp (emp_id,fname,lname) values(emp_sequence.nextval,'Darvin','Johnson');
SQL> insert into emp (emp_id,fname,lname) values(emp_sequence.nextval,'Mig','Andrews');
SQL> insert into emp (emp_id,fname,lname) values(emp_sequence.nextval,'Alex','Martin');
SQL> insert into emp (emp_id,fname,lname) values(emp_sequence.nextval,'Jon','paul');
SQL> insert into emp (emp_id,fname,lname) values(emp_sequence.nextval,'Yatin','Bones');

In emp_sequence.nextval where emp_sequence is the name of sequence we created above and nextval is a function that is used to assign the next number from emp_sequence to emp_id column in emp table.

Now let’s see the emp table.

SQL> select * from emp;
  
  EMP_ID FNAME                     LNAME
---------- ------------------------- -------------------------
         1 Darvin                    Johnson
         2 Mig                       Andrews
         3 Alex                      Martin
         4 Jon                       paul
         5 Yatin                     Bones

Now you can see using ‘sequence’ the emp_id column has auto incremented values from 1 to 5.

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.

  • Susan R.

    Why do so many other “flavors” of SQL have simple, single keywords like AUTOINCREMENT or AUTO_INCREMENT that instantly do what that huge mess of code that Oracle needs? A need to auto-increment is *VERY* common. I have it in EVERY table I have ever created in my lifetime.

    Will Oracle ever move into the 20th century? (Let alone the 21st century.)