How to create an auto increment field in Oracle?

Problem: Unfortunately oracle does not support auto_increment like mysql does. You need to put a little extra effort to get that.

Solution : 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. 


Let’s walk through an example:

STEP: 1 First, Let's create an emp table with an auto increment value on emp_id  colum field and  also as a table primary key and no null value constraints.


SQL> Create table emp ( 
emp_id  number not  null,
Name   varchar2(25),
Designation varchar2(30),
);
-- Create/Recreate primary, unique and foreign key constraints
alter table emp
add primary key (emp_id)
using index;

> Table created.

STEP: 2  Now we want  emp_id  to be an auto increment field. First we need a sequence to grab values from.

SQL> CREATE SEQUENCE emp_sequence
START WITH 1
INCREMENT BY 1;

>Sequence created.


Notes: 
Now we have created a sequence object named emp_sequence with starting value as 1 and incrementing by 1.


STEP: 3  Now we want  emp_id  to be an auto increment field. First we need a sequence to grab values from.
Now we can use that sequence in an BEFORE INSERT trigger on the table.

SQL> CREATE OR REPLACE TRIGGER emp_trigger
BEFORE INSERT
ON emp
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT emp_sequence.nextval INTO :NEW.emp_id FROM dual;
END;

> Trigger created.


Notes: 
This trigger will automatically grab the next value from the sequence we just created and substitute it into the emp_id column before the insert is completed. 

STEP: 4   Now Let's do some inserts:

SQL> INSERT INTO emp (name,designation) VALUES ('Java Pandit' ,'CEO' );

1 row created.

SQL> INSERT INTO emp (name,designation) VALUES (' Ramu Gayapaka' ,'Solution Architect' );

1 row created.

SQL> INSERT INTO emp (name,designation) VALUES ('Janaki Sontiyala', 'Project Lead' );

1 row created.

SQL> SELECT * FROM emp;

EMP_ID      NAME                 Designation
———- —————————————- —————--
   1            Java pandit             CEO
   2            Ramu Gayapaka     Solution Architect
   3            Janaki Sontiyala     Project Lead

———- —————————————- —————--


Because the sequence is updated independent of the rows being committed there will be no conflict if multiple users are inserting into the same table simultaneously.

Hope this article is helpful for you to fix auto increment field creation in ORACLE Database.



                             ***************** Good Luck ******************