Pages Navigation Menu

Coding is much easier than you think

How to create a AUTO_INCREMENT column in Oracle?

How to create a AUTO_INCREMENT column  in Oracle?

 
A lot of databases have a column autoincrement attribute or an autoincrement data type. They are used to create unique identifiers for a column. However in Oracle, there is no such thing such as “auto_increment” columns. But we can implement this feature with a Sequence and a Trigger:
 
** UPDATE: Complete JDBC tutorial now available here.
 
Table definition :
 

CREATE TABLE EMPLOYEE(
USER_ID NUMBER(5) NOT NULL , 
USERNAME VARCHAR(20) NOT NULL, 
CREATED_BY VARCHAR(20) NOT NULL);

 

ALTER TABLE EMPLOYEE ADD (
  CONSTRAINT userid_pk PRIMARY KEY (USER_ID ));

 
Sequence definition :
 

CREATE SEQUENCE userid_seq;

 
Trigger definition :
 

CREATE OR REPLACE TRIGGER userid_bir 
BEFORE INSERT ON EMPLOYEE
FOR EACH ROW

BEGIN
  SELECT userid_seq.NEXTVAL
  INTO   :new.USER_ID 
  FROM   dual;
END;
/

 

About Mohaideen Jamil


Am currently working as a Struts 2 Developer in a reputed IT Organisations. I can help you with teaching Core java and Struts 2. Follow me on Facebook or Google Plus. If you like my tutorials, consider making a donation to this charity, thanks.