Auto-increment in Oracle without using a trigger

Solution 1:

You can create and use oracle sequences. The syntax and details are at http://www.techonthenet.com/oracle/sequences.php

Also read the article http://rnyb2.blogspot.com/2006/02/potential-pitfall-with-oracle-sequence.html to understand the limitations with respect to AUTONUMBER in other RDBMS

Solution 2:

If you don't need sequential numbers but only a unique ID, you can use a DEFAULT of SYS_GUID(). Ie:

CREATE TABLE xxx ( ID RAW(16) DEFAULT SYS_GUID() )

Solution 3:

A trigger to obtain the next value from a sequence is the most common way to achieve an equivalent to AUTOINCREMENT:

create trigger mytable_trg
before insert on mytable
for each row
when (new.id is null)
begin
    select myseq.nextval into :new.id from dual;
end;

You don't need the trigger if you control the inserts - just use the sequence in the insert statement:

insert into mytable (id, data) values (myseq.nextval, 'x');

This could be hidden inside an API package, so that the caller doesn't need to reference the sequence:

mytable_pkg.insert_row (p_data => 'x');

But using the trigger is more "transparent".

Solution 4:

As far as I can recall from my Oracle days, you can't achieve Auto Increment columns without using TRIGGER. Any solutions out there to make auto increment column involves TRIGGER and SEQUENCE (I'm assuming you already know this, hence the no trigger remarks).

Solution 5:

Create a sequence:

create sequence seq;

Then to add a value

insert into table (id, other1, other2)
values (seq.nextval, 'hello', 'world');

Note: Look for oracle docs for more options about sequences (start value, increment, ...)