Auto increment Column in Oracle

To day I will show you how to create Auto increment column in oracle which may be use as Primary Key .

In My Sql and Sql Server Data bases it very easy to create auto increment column but in oracle you must have to follow some step to Create an Auto increment column.






Here's an example :
Step:-1
First we need to create a sequencer for Column
A sequence is an object in Oracle that is used to generate a number sequence.

CREATE SEQUENCE "S_TANOLI_ID"
MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  ORDER  NOCYCLE ;

Then we create the table and the required trigger:

CREATE TABLE "TANOLI" (
  "TANOLI_ID" NUMBER(10,0) NOT NULL,
  "TANOLI_NAME" VARCHAR2(1000),
  "CREATED_BY" VARCHAR2(100),
  "CREATED_DATE" DATE,
  CONSTRAINT "PK_COMMENT" PRIMARY KEY ("TANOLI_ID")
);

CREATE OR REPLACE TRIGGER "TR_TANOLI_ID"
BEFORE INSERT
ON TANOLI
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
  if(:new.TANOLI_ID is null) then
  SELECT S_TANOLI_ID.nextval
  INTO :new.TANOLI_ID
  FROM dual;
  end if;
END;
/
ALTER TRIGGER "TR_TANOLI_ID" ENABLE;

And there you go – an auto-incrementing primary key  in oracle.

Plese Feel Free to Socializer This Post

SOCIALIZE IT →

Tweet

FOLLOW US →

SHARE IT →