Auto-increment in Oracle without using a trigger

Question

What are the other ways of achieving auto-increment in oracle other than use of triggers?

Sauceda

Related 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 ); – Quechua

See IDENTITY column autoincrement functionality in Oracle 12c – Conaway

Answer 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).

Herodotus

Of 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. – Andy

Why was this answer selected? – Scrap

because 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. – Herodotus

Answer 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

Safeguard

Exactly. Caching and rollbacks make this nearly impossible... +1. – Murky

Answer 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() )

Broca

Answer 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".

Metabolize

The trigger might generate a sequence value only if :new.id is NULL, this would more closely mimic auto-increment in other database brands. – Painstaking

Note 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. – Excommunication

Answer 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, ...)

Byars

Answer 6

From 12c you can use an identity column, which makes explicit the link between table and auto-increment; there's no need for a trigger or a sequence. The syntax would be:

create table <table_name> ( <column_name> generated as identity );

Wigan

Answer 7

In addition to e.g. FerranB's answer:
It is probably worth to mention that, as opposed to how auto_incement works in MySQL:

sequences work database wide, so they can be used for multiple tables and the values are unique for the whole database

therefore: truncating a table does not reset the 'autoincrement' functionaltiy

Commines

Answer 8

If you don't really want to use a "trigger-based" solution, you can achieve the auto-increment functionality with a programmatical approach, obtaining the value of the auto increment key with the getGeneratedKeys() method.

Here is a code snippet for your consideration:

Statement stmt = null;
ResultSet rs = null;

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
                                java.sql.ResultSet.CONCUR_UPDATABLE);

stmt.executeUpdate("DROP TABLE IF EXISTS autoIncTable");

stmt.executeUpdate("CREATE TABLE autoIncTable ("
                + "priKey INT NOT NULL AUTO_INCREMENT, "
                + "dataField VARCHAR(64), PRIMARY KEY (priKey))");

stmt.executeUpdate("INSERT INTO autoIncTable  (dataField) "
                + "values ('data field value')",
                Statement.RETURN_GENERATED_KEYS);

int autoIncKeyFromApi = -1;

rs = stmt.getGeneratedKeys();

if (rs.next()) {
    autoIncKeyFromApi = rs.getInt(1);
}
else {
    // do stuff here        
}

rs.close();

source: http://forums.oracle.com/forums/thread.jspa?messageID=3368856

Townsley

Answer 9

SELECT max (id) + 1 
FROM   table

Waggish

I think it's not really concurrent friendly – Herodotus

"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... – Penurious

That's really worst practice. Never ever use such things. – Ob

What happens if the table is empty? :-( – Augustusaugy

What happens if the table is full? ;) – Sestet

What happened if there were two threads executing this query at the same time? – Apopemptic