Creating auto increment column in Oracle

Posted on by | Leave a reply

You can create autoincrement column in oracle in two ways.

Method 1 : Using sequence

Table Creation Query

 CREATE TABLE tblusers(UserId INT, UserName VARCHAR(50));

Sequence Creation Query

  CREATE SEQUENCE tblusers_userid_seq 
         MINVALUE 1 MAXVALUE 999999999999999999999999999 
         START WITH 1 INCREMENT BY 1 CACHE 20;

The above code creates a sequence for table tblusers with name tblusers_userid_seq with start value of 1 and limit 999999999999999999999999999.

Insertion Query

  INSERT INTO tblusers(UserId, UserName) VALUES (tblusers_userid_seq.nextVal, 'John')

Method 2 : Using Trigger
In Method 2 we can use triggers to carry out auto increment when rows are added to the table.

Before creating trigger you should make the auto increment column as primary key.For that the code is as below

  ALTER TABLE tblusers ADD (
  CONSTRAINT UserId_pk PRIMARY KEY (UserId));

The Creation of sequence and trigger is as follows

CREATE SEQUENCE tblusers_userid_seq;  
CREATE OR REPLACE TRIGGER tblusers_userid_trigg 
BEFORE INSERT ON tblusers 
FOR EACH ROW
BEGIN
  SELECT tblusers_userid_seq.NEXTVAL
  INTO   :new.UserId
  FROM   dual;
END;

Now during insertion there is no need to worry about the auto increment column and the insert query no need to contain the Id column as below

  INSERT INTO tblusers(UserName) VALUES ('John')

Leave a reply