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


I'm a Full stack developer of IT exp in J2EE, AngularJs, MicroServices, Docker, Spring (Boot, MVC, Cloud), Bluemix, DevOps. Follow me on Facebook or Google Plus. If you like my tutorials, consider making a donation to this charity, thanks.

Advertisements
%d bloggers like this: