How to create id with AUTO_INCREMENT on Oracle?

Answer

There is no such thing as "auto_increment" or "identity" columns in Oracle. However, you can model it easily with a sequence and a trigger:

Table definition:

CREATETABLE departments (
  ID           NUMBER(10)NOTNULL,
  DESCRIPTION  VARCHAR2(50)NOTNULL);ALTERTABLE departments ADD(CONSTRAINT dept_pk PRIMARYKEY(ID));CREATE SEQUENCE dept_seq;

Trigger definition:

CREATEOR REPLACE TRIGGER dept_bir 
BEFORE INSERTON departments 
FOR EACH ROWBEGINSELECT dept_seq.NEXTVAL
  INTO:new.id
  FROM   dual;END;/

All oracle Questions

Ask your interview questions on oracle

Write Your comment or Questions if you want the answers on oracle from oracle Experts
Name* :
Email Id* :
Mob no* :
Question
Or
Comment* :
 





Disclimer: PCDS.CO.IN not responsible for any content, information, data or any feature of website. If you are using this website then its your own responsibility to understand the content of the website

--------- Tutorials ---