CODEWITHSUNDEEP
oracle-databasetriggersauto-incrementsequences
Lakshmi
Lakshmi

Reputation: 1809

Auto-increment in Oracle without using a trigger

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

Upvotes: 23

Views: 33654

Answers (9)

Ben
Ben

Reputation: 52903

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 );

Upvotes: 2

kedar kamthe
kedar kamthe

Reputation: 8188

SELECT max (id) + 1 
FROM   table

Upvotes: -8

XpiritO
XpiritO

Reputation: 2827

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

Upvotes: 0

Reinier
Reinier

Reputation: 519

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

    Upvotes: 0

    • FerranB
    FerranB

    Reputation: 36827

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

    Upvotes: 3

    Tony Andrews
    Tony Andrews

    Reputation: 132690

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

    Upvotes: 11

    angus
    angus

    Reputation: 2367

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

    Upvotes: 16

    Salamander2007
    Salamander2007

    Reputation: 6394

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

    Upvotes: 3

    Dheer
    Dheer

    Reputation: 4076

    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

    Upvotes: 18

    Related Questions