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