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