Creating BETTER auto-increment columns in Oracle

I was explaining to a developer today how Oracle doesn’t support auto-increment columns for IDs and how you have to implement your own. I wanted to point him to a tutorial but I found that most of the tutorials have PL/SQL code with unexplained side effects. So I thought I’d post some Oracle PL/SQL code that behaves just like a MySQL or PostgreSQL auto-increment column, but which also fixes the sequence number when an application provides its own ID number for a record, which is one place where MySQL and PostgreSQL autoincrement columns fail.

Step 1: Know the names of your tablespaces. Know which tablespace you want to use for your tables and which tablespace you want to use for your indexes. Don’t assume that Oracle is going to put the table in the right place. To get a list of available tablespaces:

select tablespace_name from user_tablespaces;

Step 2: Create your table. In this example the table is built in the tablespace TDATA and the primary key index is built in the tablespace INDX.

create table example (
    id number not null,
    name varchar2(30),
    constraint example_pk primary key(id) using index tablespace INDX
) tablespace TDATA;

Note that by declaring the id column “not null” you can’t add a record with an empty ID field, ever.

Creating the primary key constraint on a separate line allows us to name the constraint, which can be handy when you’re looking through the data dictionary later on trying to figure out which constraint does what.

Step 3: Create a sequence. In this case I create an ID sequence that starts at 1 and goes on from there. If you want to start your ID sequence with a different number, or increment by some other amount, change these values.

create sequence example_id_seq start with 1 increment by 1;

Step 4: Create a trigger. This is what does the actual work setting the ID number. There are many ways to do this, here are two:

Method 1: Use a sequence-generated ID for all IDs.

create or replace trigger example_insert
before insert on example
for each row
begin
    select example_id_seq.nextval into :new.id from dual;
end;
/

Method 1 will always use a sequence-generated ID no matter what. For example:

SQL> insert into example (id, name) values (100, 'Earl');
1 row created.
SQL> insert into example (name) values ('Cleopatra');
1 row created.
SQL> insert into example (name) values ('Frankenstein');
1 row created.
SQL> select * from example;
        ID NAME
---------- ------------------------------
         1 Earl
         2 Cleopatra
         3 Frankenstein

In this case the trigger-supplied ID 1 overwrote the user-supplied id 100.

Method 2: Allow users to supply their own IDs when they want to.

create or replace trigger example_insert
before insert on example
for each row
declare
    max_id number;
    cur_seq number;
begin
    if :new.id is null then
        -- No ID passed, get one from the sequence
        select example_id_seq.nextval into :new.id from dual;
    else
        -- ID was set via insert, so update the sequence
        select greatest(nvl(max(id),0), :new.id) into max_id from example;
        select example_id_seq.nextval into cur_seq from dual;
        while cur_seq < max_id
        loop
            select example_id_seq.nextval into cur_seq from dual;
        end loop;
    end if;
end;
/

Method 2 will use the sequence-generated ID if the insert statement doesn’t supply an ID, which is what MySQL and PostgreSQL do. It also updates the sequence so that the next value of the sequence won’t collide with IDs supplied by an insert statement.

For example, let’s say I’m using Trigger Method 2 and just loaded the table with a bunch of old data, including old ID numbers.

SQL> delete from example;
3 rows deleted.
SQL> insert into example (id, name) values (200, 'Cleopatra');
1 row created.
SQL> insert into example (id, name) values (300, 'Frankenstein');
1 row created.
SQL> insert into example (name) values ('Earl');
1 row created.
SQL> select * from example;

        ID NAME
---------- ------------------------------
       200 Cleopatra
       300 Frankenstein
       301 Earl

The last record added gets an ID of 301.

Hope you find this useful.

59 Comments »

  1. Ghulam Yassen Said,

    May 7, 2009 @ 2:51 am

    dear sir/madam
    please tell me about data increment (autonumber) in oracel developer if the field is empty.
    when i press the new button it gave auto number.

    with many thanks

    Ghulam Yassen

  2. Sarwar Said,

    May 28, 2009 @ 2:49 pm

    Very good tutorial. I created auto-generated column in a table in few minutes following this tutorial.

    Thanks

  3. Earl Ruby Said,

    June 2, 2009 @ 4:47 pm

    You’re welcome!

  4. Earl Ruby Said,

    June 2, 2009 @ 5:07 pm

    Ghulam:

    What it sounds like you’re doing is creating a trigger that looks like the trigger in Method #1, which is the traditional way to make an Oracle field that behaves like an auto-number field. You can see the trigger you generated using the following SQL:

    select TRIGGER_NAME, TABLE_NAME, TRIGGER_BODY from user_triggers;

    – Earl

  5. mona Said,

    June 4, 2009 @ 11:43 am

    thanks earl!
    this works right up until i delete a row and then it doesnt pick up the increments(i.e. last insert before the delete was 5 so it should insert the next row as 6 but instead inserts it as 21). do u have any suggestions please!

  6. Earl Ruby Said,

    June 4, 2009 @ 12:00 pm

    Mona: Could you post the series of SQL statements that you used when you saw this result?

  7. mona Said,

    June 4, 2009 @ 12:10 pm

    earl,
    thanks for the quick reply. im still online trying to trouble shoot this.
    i used your exact statements (copy and pasted), i even created your example table to avoid any confusion and implemented the statements on that table. i am using oracle 10g XE if that makes a difference..
    thanks!

  8. Earl Ruby Said,

    June 5, 2009 @ 4:38 pm

    I actually meant the insert/delete statements you used. I wasn’t clear on when you were setting the ID and when you were letting the trigger set the ID.

    If you ever set ID 20, the next record will have a 21. Deletions won’t affect this.

    More likely what you’re seeing a side effect of the sequence cache value. Since Oracle uses a default sequence cache value of 20, if you were on #5 and you had a server failure the then the next number in the sequence could be 21. See http://www.techonthenet.com/oracle/sequences.php. I’m not sure what else you can do to exhaust the cache (restarting Oracle doesn’t do it), which is why I was asking about the insert/delete statements you used.

    You might try recreating the sequence with the “nocache” option and see if you still see this behavior, although I wouldn’t recommend that if you’re working on a database with a large amount of transaction activity.

  9. Rakesh Kumar Said,

    July 15, 2009 @ 12:36 am

    This suggestion is quite useful if we are inserting records in a single table. However, what would happen if the auto incrementing column is a primary key in the header table and the same is being referenced in the detail table. Also we want to insert records in both header and detail tables in one transaction.

  10. Earl Ruby Said,

    July 15, 2009 @ 11:09 am

    Rakesh: The triggers still work exactly the same in the case you’re describing. What you want to do is to use the “returning” clause on your insert statement to return the primary key column’s new value, use that as the foreign key in your detail table, then commit after all of the records have been inserted (in a single transaction). Make sure that autocommit has been turned off in your session.

    When I’m writing software I usually call a utility function to open the database connection and set up the initial session state so it’s always the same: autocommit off, NLS_DATE_FORMAT=’YYYY-MM-DD HH24:MI:SS’, etc. You can do the same thing with login.sql to ensure that your sqlplus sessions always start with a known state. If you do that and all parts of your application use the same “get database connection” utility function then your SQL statements will always execute with the environment in a known state.

  11. Andy Said,

    November 18, 2009 @ 5:05 am

    how would i do this if my table already exists ? i cant be adding the id column since it is marked non-nullable.

  12. Earl Ruby Said,

    November 18, 2009 @ 11:37 am

    Andy:

    Add the ID column as nullable field.

    Update the ID column with a unique ID value. (update tablename set id = rownum where id is null;)

    Alter the table and make the ID column non-nullable.

    Alter the table and add the unique constraint.

    – Earl

  13. Duncan Jack Said,

    February 5, 2010 @ 10:03 am

    Superb. Clearly written, very approachable.

    Thank you.

    Duncan

  14. TimeJunky Said,

    March 18, 2010 @ 5:40 am

    Thx, but it is not clear for me where you mean ID as the keyword or where id as the name of the column.

    e.g.
    “if :new.id ”

    Have I change this to new.group_id in the case my column is called grop_id?

    In my case the table hast the columns
    id, group_id, name
    id = autoincrement
    group_id takes another sequence

    Thx in advance!

  15. Earl Ruby Said,

    March 18, 2010 @ 3:58 pm

    :new refers to the record/row that triggered a “for each row” trigger.

    In the statement “select greatest(max(id), :new.id) into max_id from example;” the max(id) is the maximum value of the id field looking at the entire table, :new.id is the value of the id field of the row that triggered the trigger.

    The statement “if :new.id” refers to the value of the ID field of the row that triggered the trigger.

    You might want to review a PL/SQL tutorial and read up on :old and :new. There are nuances in their use that I’m not covering here.

  16. SJari Said,

    April 9, 2010 @ 3:59 am

    Thanks allot :-)

    as Oracle is new for me ( coming from MySQL) I have to say that I’m amazed that Oracle did not have such function!

    but Thanks allot :-) you just made my day ;-)

    cheers

  17. SJari Said,

    April 9, 2010 @ 4:43 am

    I have just one question?

    create or replace trigger example_insert
    before insert on example
    for each row
    begin
    select example_id_seq.nextval into :new.id from dual;
    end;

    what is new.id ???
    what is dual ???

    what do they present, and how do I modify them to my need (just out of curiosity)?

  18. Earl Ruby Said,

    April 10, 2010 @ 11:52 am

    That’s actually three questions.

    :new represents the new record, so :new.id is the id field of the new record that you’re inserting.

    See http://www.dba-oracle.com/sql/t_dual_table.htm for more info.

  19. Eduardo F. Sandino Said,

    April 29, 2010 @ 11:38 am

    Hi Earl, first of all thanks for your article…

    Just i have one question.
    Is really necesary to put “using index tablespace INDX” the tablespace when we are defining the index ?

  20. Earl Ruby Said,

    April 30, 2010 @ 4:53 pm

    If you want to force Oracle to use a particular tablespace for your index then yes, it’s necessary.

    If you don’t care what tablespace Oracle sticks your index into then you can leave that part off.

  21. Frank Said,

    August 15, 2010 @ 11:39 pm

    Why would PostgreSQL fail in this situation? You can create (almost) the same trigger and function in plpgsql to do exactly the same trick as you do in Oracle.

  22. pellicle Said,

    August 18, 2010 @ 5:48 pm

    very useful, thanks for your succinct post here.

  23. hawa Said,

    August 24, 2010 @ 7:12 pm

    eloo..
    i got problem..i run sequence code are created..but when i want to create trigger in TOAD he prompt me to insert value??
    what value should i put in dialog box..tq

  24. Earl Ruby Said,

    August 26, 2010 @ 4:58 pm

    Frank: I was referring specifically to the typical MySQL auto_increment and Postgres “SET DEFAULT NEXTVAL(‘some_sequence’)” usage, which is also the typical way that you implement an auto-increment sequence in Oracle. In all three cases the sequence will fail to insert a new record if the ID already exists.

    You could definitely implement this as a trigger in Postgres.

  25. Earl Ruby Said,

    August 26, 2010 @ 5:09 pm

    Hawa: I don’t use TOAD, but I’d guess it wants the starting sequence value. Try entering “1″ and see what happens.

  26. arun Said,

    September 8, 2010 @ 12:52 am

    Hi Earl,

    Thnx for the article :)

    I hav a question. Why did you go for getting the next value for the sequence in the below statement when current value seems to be workable?

    “select example_id_seq.nextval into cur_seq from dual;”

  27. Dili S Said,

    October 7, 2010 @ 4:43 am

    Tank you very much for getting a clear picture about auto-increment in Oracle

  28. eros sy Said,

    November 18, 2010 @ 10:00 pm

    Thanks for the helpful scripts but… some results were not expected.

    I executed the below script by order

    create table example (
    id number not null,
    name varchar2(30),
    constraint example_pk primary key(id)
    );

    create sequence example_id_seq start with 1 increment by 1;

    create or replace trigger example_insert
    before insert on example
    for each row
    declare
    max_id number;
    cur_seq number;
    begin
    if :new.id is null then
    — No ID passed, get one from the sequence
    select example_id_seq.nextval into :new.id from dual;
    else
    — ID was set via insert, so update the sequence
    select greatest(max(id), :new.id) into max_id from example;
    select example_id_seq.nextval into cur_seq from dual;
    while cur_seq < max_id
    loop
    select example_id_seq.nextval into cur_seq from dual;
    end loop;
    end if;
    end;
    /

    select * from example;

    insert into example (id, name) values (100, 'Earl');
    insert into example (name) values ('Cleopatra');
    insert into example (name) values ('Frankenstein');

    commit;

    select * from example;

    Results:
    1) table created
    2) sequence created
    3) trigger created
    4) successfully executed but no records (expected)
    5) 1 record inserted
    6) 1 record inserted
    7) 1 record inserted
    4) successfully executed with 3 records (expected)
    but the id field's value are not

    ID,NAME
    100,Earl
    2,Cleopatra
    3,Frankenstein

    Possible to fail when the sequence reaches the 100.

    Please give a comment on this.

    my env:
    Oracle 11g

  29. eros sy Said,

    November 18, 2010 @ 10:05 pm

    additional, here’s the sequence details

    CREATED 10-11-19
    LAST_DDL_TIME 10-11-19
    SEQUENCE_OWNER xxx
    SEQUENCE_NAME EXAMPLE_ID_SEQ
    MIN_VALUE 1
    MAX_VALUE 999999999999999999999999999
    INCREMENT_BY 1
    CYCLE_FLAG N
    ORDER_FLAG N
    CACHE_SIZE 20
    LAST_NUMBER 21

  30. eros sy Said,

    November 18, 2010 @ 10:17 pm

    additional, please take a look with my experiment.

    continuation with the previous post, I tried to execute the following scripts in order:

    insert into example (id, name) values (1000, ‘Earl2′);

    insert into example (name) values (‘Cleopatra2′);

    select * from example;

    INSERT Results:
    1 record inserted
    1 record inserted

    Select Result:
    ID NAME
    ———————- ——————————
    100 Earl
    2 Cleopatra
    3 Frankenstein
    1000 Earl2
    1001 Cleopatra2

    Conclusion:
    it fails only on the very first time.

  31. eros sy Said,

    November 18, 2010 @ 10:20 pm

    and here’s the another test (expected):

    truncate table example;
    insert into example (id, name) values (100, ‘Earl’);
    insert into example (name) values (‘Cleopatra’);
    insert into example (name) values (‘Frankenstein’);
    commit;

    1 record inserted
    1 record inserted
    1 record inserted
    commit succeed

    ID NAME
    ———————- ——————————
    100 Earl
    1003 Cleopatra
    1004 Frankenstein

    note: correction on November 18, 2010 @ 10:17 pm post..
    before the SELECT statement, I successfully executed the commit.

  32. eros sy Said,

    November 18, 2010 @ 10:44 pm

    Yes, I confirmed. The cause is the greatest function failed to get compare when the target table has no record.

    try this:

    truncate table example;
    select greatest(max(id), 100) from example;

    Result2:
    truncate table example was successfully executed.
    GREATEST(MAX(ID),100)
    ———————-

    1 rows selected

    …but the expected value is 100.

    I think the behavior of greatest function is different if encountered NULL.

    here’s the link: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:524526200346472289

  33. eros sy Said,

    November 18, 2010 @ 10:55 pm

    Here’s the revised trigger script of Method 2.
    Add NVL for NULL checking. This is recommended only if the field is a number.

    create or replace trigger example_insert
    before insert on example
    for each row
    declare
    max_id number;
    cur_seq number;
    begin
    if :new.id is null then
    — No ID passed, get one from the sequence
    select example_id_seq.nextval into :new.id from dual;
    else
    — ID was set via insert, so update the sequence
    select greatest(NVL(max(id), 0), :new.id) into max_id from example;
    select example_id_seq.nextval into cur_seq from dual;
    while cur_seq < max_id
    loop
    select example_id_seq.nextval into cur_seq from dual;
    end loop;

    end if;
    end;
    /

  34. Earl Ruby Said,

    November 19, 2010 @ 11:26 am

    You are correct, on an empty table if the very first record inserted had an ID passed the max(id) would return null. nvl() is the correct way to fix that problem.

    Good catch. I’ll update the example code.

  35. My Readings This Week – Ganda Manurung Said,

    December 9, 2010 @ 12:50 am

    [...] Creating Auto-Increment Columns in Oracle. The alternatives article. [...]

  36. Kunal Said,

    January 9, 2011 @ 5:53 am

    Very simple and crisply written article on using Triggers and Sequences !

  37. Sweetie Bracelet Said,

    February 6, 2011 @ 10:38 pm

    ,-~ thank you for posting a topic about this stuff, i was looking for it. ,-*

  38. Mariyappan.C Said,

    May 19, 2011 @ 10:43 pm

    Auto increment of the column in 11G without any trigger only applicable in 11g

    CREATE SEQUENCE “CM_4_0″.”TEST_SEQ” MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 2 START WITH 1000031624 CACHE 20 NOORDER NOCYCLE ;

    create or replace function F return number as
    a number;
    begin return
    select TEST_SEQ.NEXTVAL into a from DUAL;
    return a;
    end;

    drop table Z

    create table Z( name varchar2(10), PRI number generated always as(f()) VIRTUAL );

    insert into z(name)values(‘asasa’);
    insert into Z(name)values(‘asasa’);
    insert into z(name)values(‘asasa’);
    insert into z(name)values(‘asasa’);

    commit;

  39. Michele Said,

    July 26, 2011 @ 4:26 am

    thx for the article!

    Performance of Method2 is the same of Method1?

  40. Earl Ruby Said,

    August 3, 2011 @ 11:27 am

    Michele:

    If you’re worried about performance test it and see. Try inserting a million records into a database using the method1 trigger, then try it again with the method2 trigger. Time the results.

    Let us know what you find.

  41. Creating auto-incremented PK column in Oracle as we know it from SQL Server « Jacek Spólnik's blog Said,

    September 17, 2011 @ 2:27 am

    [...] can’t be complicated task. But Oracle suprised me again, so I looked for some solution. The post, which I found, describes how to do that in very clever way. And now, everything is simpler [...]

  42. Bryan Klumpp Said,

    October 20, 2011 @ 10:05 pm

    I was having the same problem as mona with Oracle 10g XE. However I’m not sure it is a XE-specific issue. I suspect it is related to the JDBC driver not handling the semicolons properly; it looks like it is trying to process it as multiple SQL statements. My guess is that mona was trying to run in a JDBC-based tool like DB Visualizer or SQL Developer. There may be a way to properly escape the semicolons but I gave up looking for it and found an easier way: try executing from SQL Plus, it worked for me.

  43. justus the octopus Said,

    November 26, 2011 @ 2:26 am

    im so grateful for this post i had this problem for 3 good days. when the autonumber column is a string then its a problem if the table has no data but it works well when its a number.

  44. siddharth sinha Said,

    February 1, 2012 @ 1:32 am

    there is better option then creatin a trigger

    SQL> insert into example (id, name) values (example_id_seq.nextval, ‘Earl111′);
    1 row created.

    here we are not suppore to use trigger. nextval will take care fo the same.

  45. Roland Said,

    May 7, 2012 @ 11:01 am

    Method 2 also has an interesting issue if a user decides to put in all 9′s for a number, either to have something show at the bottom of a list or because they want something easy to remember. Would be better to see if the new id from the sequence is already in the database [as in your example, where the seq.nextval would be 300 and 300 has already been entered for a previous record by the user]and find the next unused value [loop through the .nextval until you find one that hasn't been used].

  46. Mohammed Said,

    June 3, 2012 @ 4:23 am

    thanks, very useful article

  47. bread butter Said,

    August 28, 2012 @ 2:15 pm

    I tried using method one with some modifications and got some problems. I use oracle 11g and sql developer to learn.
    When i tried to create the trigger, i got a diaglog box asking me to “enter bind values”. I just did what the box said without looking too much into it.
    The trigger was created with a warning. I don’t know if this is dangerous.
    The details are posted to stack overflow -

    http://stackoverflow.com/questions/12166046/creating-a-trigger-in-oracle-express/12167586#12167586

    http://stackoverflow.com/questions/12167191/when-i-create-a-trigger-i-get-warning-execution-completed-with-warning

    Please help me to fix this.
    Thanks in advance.

  48. Earl Ruby Said,

    August 28, 2012 @ 3:13 pm

    :new is a type of bind variable, but in the context of the trigger it represents the newly-created record. I don’t use SQL Developer. Sounds like it wants to interpret :new as an interactive bind variable, which is not what it is.

    Try creating the trigger in an SQLPLUS session.

    Put your code into a text file such as “better_increment.sql”, start sqlplus, then type “@better_increment.sql” to run the code in the file. If you made any typos and get an error type “show errors” to find the mistake.

  49. kalyani Said,

    September 4, 2012 @ 10:49 am

    can u help me how to use varchar auto increment in procedures like using auto increment numbers ex: select nvl(max(id,100)+1) into n from dual;
    i want to start the auto increment value like T0001, how can i do this, plz help me………..

  50. Stuarty Said,

    September 25, 2012 @ 8:14 pm

    Awesome tutorial!

    Thanks a lot! :-)

  51. Deb88 Said,

    October 13, 2012 @ 2:03 pm

    Dear Sir ,
    i want to know that , how i can create a auto increment id field which will set to 1 after deleting all the rows from the table .. please help me out … thanks

  52. Evert Said,

    November 5, 2012 @ 8:45 am

    I’d appreciate you help me to solve a problem. I have imported a database to my laptop, but it fail when I insert a row in the tables that has a auto increment index. Before, when I did the import the process failed because Oracle don’t found a tablespace. I created to hand this tablespace and I get did the import process successful. You may to help me with my problem?

  53. Earl Ruby Said,

    November 11, 2012 @ 10:41 pm

    What error message do you get when the insert fails?

  54. Wil Said,

    February 6, 2013 @ 9:24 pm

    While it is easy to understand it’s truly awful in terms of efficiency. With method 2 if I add a couple of nulls and then add 10000000 it will loop about 10 million times until the sequence has moved on – not very nice!

    It could be worse. If the user adds negative values luckily the sequence is considered. You hate to have a “wait until the sequence reaches -1, incrementing upwards”.

  55. Earl Ruby Said,

    February 7, 2013 @ 3:10 pm

    So don’t do that.

  56. Susan R. Said,

    July 16, 2013 @ 7:33 am

    Why do so many other flavors of SQL have simple, single keywords like AUTOINCREMENT or AUTO_INCREMENT that instantly do what that huge mess of code that Oracle needs? A need to auto-increment is *VERY* common. I have it in EVERY table I have ever created in my lifetime.

    Will Oracle ever moving into the 20th century? (Let alone the 21st century.)

  57. Abdullah Al Mamun Said,

    July 23, 2013 @ 1:45 am

    I want new record automatically increment id form builders code;
    Please help me.

  58. T Said,

    September 19, 2013 @ 8:05 am

    hey i have this error when try to insert new row to my table after doing everything with method 1, the error is ORA-04098 trigger is invalid and failed re-validation.
    and my trigger is,
    create or replace trigger CCMSDBA.Scheduled_Id_insert
    before insert on ccmsdba.schedule_activity
    for each row
    begin
    select ccmsdba.scheduled_id_seq.nextval into :new.scheduled_id from dual;
    end;
    /

  59. Earl C. Ruby III Said,

    September 19, 2013 @ 10:47 am

    T: You should also get that error when you create the trigger. Try creating the trigger, then type “show errors” to find out what Oracle doesn’t like. You should get the line number that Oracle is choking on and a more detailed error message.

Leave a Comment