MySQLs auto_increment in Oracle

MySQL has a reserved keyword "auto_increment" that can be used in the ID/primary key column of a new table. Whenever a new record is added to the table, this field will be automatically set to an unique incremented integer value.

In the Oracle database, this feature is missing. But we can simulate this feature using the SEQUENCE and TRIGGER functionality provided by Oracle.

A sequence is a sequential value stored in the database. It starts with an initial user supplied value, and can be incremented by an arbitrary value to a given maximum value. The statement to create a sequence for table ATABLE:

create sequence ATABLE_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

Now, we want to hook up a trigger on an INSERT event of the table. Whenever a record is inserted into the table, the current value of the sequence is incremented and set in the primary key field of the inserted record.

create or replace trigger ATABLE_TRIG
before insert on ATABLE
for each row
begin
select atable_seq.nextval into :new.id from dual;
end;