Create an auto-increment field in Oracle

If we do not already have a table to work with, we will create a sample table for this purpose.

create table schema.test_table (
	row_id		number(5),
	name		varchar2(50),
	status		varchar2(1),
	entry_date	date
);

Let us say we want to make the “row_id” field an “auto-increment” field. To do so, we need a sequence object first. The code below gives us an sequence that starts off at 1 and capped at 99999.

create sequence schema.seq_test_table_row_id
minvalue 1
maxvalue 99999
start with 1
increment by 1
nocache;

All we have left is the actual “auto-increment” part. To do this, we build a very simple trigger on the table.

create trigger schema.trg_test_table_row_id
before insert on schema.test_table
for each row
begin
	if (:NEW.row_id) is null then
		select schema.seq_test_table_row_id.nextval into :NEW.row_id from dual;
	end if;
end;

Now, when you insert into the table schema.test_table, if you wish to use the sequence object as an auto-incrementer, just do not enter the row_id field. Two examples of using this auto-incrementer below:

insert into schema.test_table (name, status, entry_date) values('John Doe','1',sysdate);
insert into schema.test_table values(null,'John Doe','1',sysdate);

What if we want to override this trigger and use a number of our choice for the row_id field? No problem, just insert the value as you would normally. Note the trigger has an if-clause in it; the trigger will only have an effect (ie. auto-increment from the sequence) if the incoming row_id field is not populated.