Auto-increment in Oracle without using a trigger
Solution 1
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 2
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 3
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 4
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 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, ...)
Related videos on Youtube
Comments
-
Lakshmi almost 2 years
What are the other ways of achieving auto-increment in oracle other than use of triggers?
-
brofield over 15 yearsRelated question: Is it possible to create a sequence and then set the nextval method as the default value? i.e. create sequence seq; create table foo ( mycol number default seq.nextval );
-
Lalit Kumar B almost 9 years
-
-
Bill Karwin over 15 yearsThe trigger might generate a sequence value only if :new.id is NULL, this would more closely mimic auto-increment in other database brands.
-
Powerlord over 15 yearsNote that you need a FOR EACH ROW or else :new is not accessible... or at least that's what my textbook said when I took a class in PL/SQL.
-
Dan Vinton over 15 yearsExactly. Caching and rollbacks make this nearly impossible... +1.
-
Salamander2007 over 15 yearsI think it's not really concurrent friendly
-
RussellH over 15 years"I think it's not really concurrent friendly" I'll second that. I've seen web applications that were coded this way do all sorts of interesting things...
-
Admin about 15 yearsOf course you can. You create an Insert procedure that gets the nextval. you revoke Insert on that table and grant execute on that proc/package. No trigger needed.
-
Lordn__n about 15 yearsWhy was this answer selected?
-
Salamander2007 about 15 yearsbecause it's true. You cannot do plain INSERT and achieve the same effect as autoincrement without the use of Trigger and Sequence. Mark Brady Answer is also true, if you consider Stored Proc as a plain insert.
-
Marius Burz over 14 yearsThat's really worst practice. Never ever use such things.
-
wonea over 12 yearsWhat happens if the table is empty? :-(
-
WOUNDEDStevenJones over 10 yearsWhat happens if the table is full? ;)
-
Eki over 10 yearsWhat happened if there were two threads executing this query at the same time?