Search the Site

Sponsors

bottom corner

Create an auto-increment field in Oracle

One of the little convenient things that MySQL has is that, when defining a table, a key field can be made to auto-increment, so that we do not have to worry about populating that field. Oracle takes a different approach with sequences being their own objects. With this script, we can emulate this feature in Oracle while still enjoy having an independent sequence object.

This page is filed under keyword(s): 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.

Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.

This page is filed under keyword(s): oracle.
Author: C. Peter Chen
Last updated: 25 Mar 2008

bottom corner