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.