How to Create Auto Increment Columns in Oracle

OracleAfter pointing out how not to create auto increment columns in Oracle, I suppose I should point out how to create auto increment columns in oracle.

Many will gripe about this not being a standard feature in Oracle, but when it’s as easy as two more commands after your CREATE TABLE command I can’t see any good reason to use fancy SQL on every insert.

First let’s create a simple table to play with.

SQL> CREATE TABLE test
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));

Table created.

Now we’ll assume we want ID to be an auto increment field. First we need a sequence to grab values from.

SQL> CREATE SEQUENCE test_sequence
START WITH 1
INCREMENT BY 1;

Sequence created.


Now we can use that sequence in an BEFORE INSERT trigger on the table.

CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT
ON test
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT test_sequence.nextval INTO :NEW.ID FROM dual;
END;
/

Trigger created.

This trigger will automatically grab the next value from the sequence we just created and substitute it into the ID column before the insert is completed.

Now we’ll do some inserts:

SQL> INSERT INTO test (name) VALUES ('Jon');

1 row created.

SQL> INSERT INTO test (name) VALUES ('Bork');

1 row created.

SQL> INSERT INTO test (name) VALUES ('Matt');

1 row created.

SQL> SELECT * FROM test;

ID NAME
---------- ------------------------------
1 Jon
2 Bork
3 Matt

Because the sequence is updated independent of the rows being committed there will be no conflict if multiple users are inserting into the same table simultaneously.

If you need to capture the value of the auto increment column you’ll want to check out my other article Oracle Auto increment Columns – Part 2

Matt has posted a similar method in which he uses a sequence in the insert eliminating the need for the trigger. That will work just as well without the need for the trigger. The only drawback to this method is slightly longer SQL commands.

67 thoughts on “How to Create Auto Increment Columns in Oracle”

  1. There is an issue caused by using directly the increment of a sequence in an insert statement

    insert into table (id) values (seq.nextval)

    is okay, but will not make inserts work in the table in some case.
    For example, I had to use and MS Access interface to insert rows in an oracle db. Using the statement above will not work.

    You’ll have to use the trigger one behaivor, demanding the sequence increment directy to the oracle server.

    Hope this helps 😉

  2. 1. Works great, thanks for posting this.
    2. These two lines were not needed:

    DECLARE
    seqNo NUMBER;

    I removed them and the trigger still works correctly.

    Please let me know if they are really needed (ie, is this going to bite me later?)

    3. I don’t understand the line:
    REFERENCING NEW AS NEW OLD AS OLD

    so if you have any explanation, I would be greatful.

    Thanks again,
    Tony Jackson
    atjsfo@hotmail.com

  3. Tony, glad this helped you.

    The DECLARE portion must be left from some previous revision of some other trigger. It’s not necessary and I will be removing it form the code above. Thanks for pointing this out.

    The REFERENCING NEW AS NEW OLD AS OLD gives you the :NEW and :OLD objects so you can refer to columns as :NEW.id or :NEW.name. If it’s an insert there is really just a :NEW, but for an update you would have the new values in :NEW and the old values in :OLD.

    Hope that all makes sense. Let me know if you’re looking for more.

  4. Update: Thanks to Tony for pointing out a couple things that could be tightened up.

    I left out the OLD AS OLD since there is no OLD data on inserts. The NEW AS NEW could also be excluded, but it’s nice to be explicit here. I also took out the DECLARE line where I declared a variable I never used.

  5. Great post!

    Sometimes I do this in PL/SQL:
    nextID := test_sequence.nextval;
    insert into test (ID,name) values (nextID,’bzzt’);
    …use nextID for other work

    I’d like to use your trigger solution, but was wondering about the best way to capture the most recent ID.

    Any thoughts?

    Thank you!

  6. I’m doing a java program that interfaces with an oracle database and require to know how to get the id generated for the last insert for use in later processing.

    Any thoughts would be great.

    Shaun

  7. Any clue how I can easily and reliably grab that id number in PHP? In PHP with MySQL I would use the function mysql_insert_id() to grab it. However, there is obviously no direct equivelant in Oracle I’m aware of. Thoughts?

  8. I copied your codes (create table, sequence and trigger). The first two (table and sequence) worked, but the creating of trigger failed. The error message is “trigger created with PL/SQL compilation error”. And could insert any vales to tables due to fault trigger. Any hints? By the way, I used MS_Query to execute those SQL statements.

  9. D, what version of Oracle are you using? I’m not familiar with MS_Query, but some apps don’t like having a semicolon at the end of a PL/SQL block (before the forward slash).

    Try again and then run the command show errors and that should give you some better error messages. Good luck!

    Jon

  10. Thanks for your reply.

    The version of Oracle is 8.1

    I tried to create the trigger without simicolon but it still doesn’t work. I tried “show errors”, but was told “Invalid SQL statement”. Because of that invalid trigger, I couldn’t insert any information into the table. Any more hints? Thanks.

  11. Try it through SQL*Plus if possible. At least then the ‘show errors’ command should work.

    I would think this would work just fine in 8.1 but its been a while since I worked with anything prior to 9i.

    Jon

  12. I’m getting the following error when I try the above trigger with my table.
    java.sql.SQLException: ORA-04098: trigger ‘CCTP_WDB.PROD_TRIGGER’ is invalid and failed re-validation

    Prod_trigger is the name of my trigger. The rest is the same.
    I’m using JSP to run the query and to connect to the database. PLease help.

  13. Keyur,

    Try running the trigger creation in SQLPlus or SQL Developer. If the trigger still gives that error type ‘show errors’ and you should get a more meaningful error message.

    It sounds likely that the sequence didn’t create properly.

    Jon

  14. Hey, thanks for the post. I’m getting “PLS-00049: bad bind variable ‘NEW.ID'” when I run these create statements in SQL*Plus.

    Am using ‘REFERENCING NEW AS NEW’ and ‘SELECT… INTO :NEW.ID FROM DUAL’.

    Any ideas? Thanks!
    michael

  15. I had the same problem as Keyur. I was attempting to create my triggers using Squirrel (which I have to say is an excellent program, but not particularly good for proceedures). I found that Squirrel couldn’t interpret the commands, and was therefore creating invalid triggers, and wouldn’t tell me why. The Show errors command didn’t work. So I downloaded another excellent program called TOAD, which allowed me to create the triggers and get everything working. It has an excellent interface with SQL window and proceedure window. It also has a wizard type feature which helps you create triggers or a series of other statements if you are a novice or unsure of the syntax.

    I thought other users/readers might find this information useful.

    Stu

  16. Stuart,

    Glad TOAD worked for you. We used to use it heavily for development but have recently switched to Oracle’s SQL Developer. Check it out. It has much of the functionality of TOAD with less clutter.

    Thanks for sharing.

  17. Hi Jon, thank you for taking the time to address sequencing. I have a variation of the code above that I’m trying to write. It would dynamically identify the number by which I have to ALTER the incremental value of a SEQUENCER in Oracle. My scenario is that we have an external batch load of data into the data warehouse that is not using the sequencer.. The SEQ field is manually being set to begin at the max+1 of the value in the table. So every night I have to reset the sequencer to be the max value of that same table to account for the recently added records.

    So I’ve got all the pieces I need, but when I put them together inside a stored procedure I get a syntax error with no explanation. Here’s the code. I must be missing something really simple that I hope you will see easily. If you have a moment, I’d appreciate your insight. Thanks. Mike

    /* I just keep this code here for safe keeping – ignore for now
    create table MGA_AUDIT_SEQ (
    MGA_POLICY_MAXVAL NUMBER
    , MAX_POL_SEQ_NEXTVAL NUMBER — THIS IS THE VALUE IN CACHE FOR THE SEQUENCER
    , SEQ_DIFF NUMBER — THIS IS THE DIFFERENCE BETWEEN THE 2 ABOVE VALUES
    , LOAD_DATE VARCHAR2(10) — JUST SYSDATE IN MM/DD/YYYY FORMAT
    );
    COMMIT;
    */

    create or replace procedure sp_alter_mga_pol_seq
    is
    newseq number;
    nextseq number;
    mgapolval number;
    begin

    SELECT MAX(SEQ_ID) INTO MGAPOLVAL from mga_policy;
    SELECT MGA_POL_SEQ.NEXTVAL INTO NEXTSEQ FROM DUAL;
    INSERT INTO MGA_AUDIT_SEQ
    VALUES (MGAPOLVAL,
    NEXTSEQ,
    NEXTSEQ-MGAPOLVAL,
    TO_CHAR(SYSDATE, ‘MM/DD/YYYY’));

    –SEQ_DIFF is a value I have determined in the temp table I
    –created above

    select mga_pol_seq.currval + seq_diff +1
    into newseq
    from dual, mga_audit_seq;

    — set the sequencer to NOCACHE

    ALTER SEQUENCE MGA_POL_SEQ
    NOCACHE;
    COMMIT;

    — reset the sequencing number to a new number
    — which is now set in the variable NEWSEQ

    ALTER SEQUENCE MGA_POL_SEQ
    INCREMENT BY newseq ; —
    COMMIT;

    — HAVE TO CALL THIS HERE IN ORDER TO MAKE THE CHANGE AFFECTIVE

    select mga_pol_seq.nextval from dual;

    — bring the sequencer parameters back to their original state

    ALTER SEQUENCE MGA_POL_SEQ
    CACHE 20
    nominvalue
    increment by 1 ;
    COMMIT;
    end;

  18. Hey there

    GREAT SITE! Im moving over from MySQL so this site is extremely helpful for me.

    Just one question, I was thinking of creating a sequence that I could use on lots of other tables, so say:

    CREATE SEQUENCE auto_increment
    START WITH 1
    INCREMENT BY 1;

    Then later for all my tables I would just use that sequence called auto_increment, however my question is as follows:

    Would using the same sequence for all my other tables that need to be incremented be effected by other uses of it. As in say another table was using that sequence, would it effect my current table who is also using that same sequence? or would it be best to create a special sequence for every table that needs one.

    Thanks
    Dave

  19. Hopefully you might know what has happened to me here, I am getting a compilation error:

    I created a table, created a sequence and then created the trigger in exactly the same way as your site describes, but it failed with the following errors:

    Warning: Trigger created with compilation errors.

    so I ran:

    SQL> show errors trigger trig_users_auto_increment
    Errors for TRIGGER TRIG_USERS_AUTO_INCREMENT:

    LINE/COL ERROR
    ——– —————————————————————–
    2/46 PLS-00049: bad bind variable ‘NEW.ID’

    ——-

    Any ideas why it doesn’t like the variable new?

    Here is a history of what I created:

    CREATE TABLE users (
    userID NUMBER PRIMARY KEY,
    email VARCHAR2(100),
    fname VARCHAR2(50),
    sname VARCHAR2(50)
    )

    ——– AUTO_INCREMENT

    CREATE SEQUENCE seq_users_auto_increment
    START WITH 1
    INCREMENT BY 1;

    ——– trigger

    CREATE OR REPLACE TRIGGER trig_users_auto_increment
    BEFORE INSERT
    ON users
    REFERENCING NEW AS NEW
    FOR EACH ROW
    BEGIN
    SELECT seq_users_auto_increment.nextval INTO :NEW.ID FROM dual;
    END;

    Thanks in advance for your help!!!!
    Hugs
    David
    Dublin Ireland

  20. ops… I had a blonde moment! the reason it didn’t work was ID was not in my users table, it is userID not ID 🙂

    Well I hope I helped something out there who may have had that highlighted moment 🙂

  21. Michael,

    You should try running this in SQL*Plus and after you get “procedure
    compiled with compilation errors” or an error like this (I’m guessing
    that’s what you’re getting) type show
    errors
    .

    That should give you a more useful ORA- error. I’m guessing the
    problem you’re having is with the ALTER SEQUENCE command.
    That is DDL (data description language), language that defines or
    changes the objects and structures in the database. PL/SQL has
    trouble with DDL and most ALTER, DROP and
    CREATE commands will result in an error when they are in
    PL/SQL blocks.

    You might need to use an EXECUTE IMMEDIATE command to make
    this happen.

    Hope this helps!

  22. Thank you very much for this clarification. It has been of great help. There is none the less one thing that I do not understand (althought your code works perfectly in my DB) is the DUAL keyword. What is its purpose?

    Thank you very much.

  23. http://www.adp-gmbh.ch/ora/misc/dual.html

    Found this explanation of dual.

    dual is a table which is created by oracle along with the data dictionary. It consists of exactly one column whose name is dummy and one record. The value of that record is X.
    rene@ora92> desc dual
    Name Null? Type
    ———————– ——– —————-
    DUMMY VARCHAR2(1)

    rene@ora92> select * from dual;

    D

    X
    The owner of dual is SYS but dual can be accessed by every user. As dual contains exactly one row (unless someone fiddled with it), it is guaranteed to return exactly one row in select statements if a constant expression is selected against dual, such as in:
    select sysdate from dual
    Although it is possible to delete the one record, or inse

  24. Hi,

    Do you think there will be any issues with using a sequence and triggers in an OLTP as there involves a context switch when executing a trigger in the database?. In my approach I use my ORM layer (TROQUE) to generate the id, and it has served me well.

    thanks

  25. Nadeem,

    There should not be an issue if a sequence is used. If you grab nextval from a sequence and some one else grabs one even before you have committed your changes they will receive the next number in the sequence. Even if you do not use the value you retrieve from the sequence it is yours forever.

    I’m not familiar with TROQUE, but if you’re worried you should probably ask their support about this?

    Hope this helps!

  26. The trigger method guarantees that you can never have a duplicate primary key.

    Using the sequence in the insert statement method works, until someone does an insert without using the sequence.
    Lets say you have one row in your table, with ID = 1. Someone decides they need to have a record with ID = 10, and so they insert using insert into table (id) values (10). You now have a ticking timebomb – the sequence will continue to increment 2, 3 etc, until it reaches 10 at which time the insert fails with
    ERROR at line 1:
    ORA-00001: unique constraint (SYS.SYS_C002948) violated

    However using the trigger method, if someone tries to specify an explicit ID value, the trigger will overwrite the specified value with the value from the sequence.
    I guess there is some performance gain from not using the trigger, so if you trust all the code that accesses your database and performance is critical, the sequence in the insert statement method may be what you need. Otherwise, the trigger method is safest, and also means you can be lazy when inserting.

  27. All good points Sam. From a data architecture standpoint my tendency is to not trust the developers, especially in big organizations. Either is valid, just one tends to be riskier than the other.

  28. Hi,
    let me know how can get the sum of first and second sal ,second and third sal ,third and fourth sal and so on.. from emp table.please give me the SQL statement for this query

  29. “Many will gripe about this not being a standard feature in Oracle”

    I am reading this when I should be onto my next task.
    GRIPE…GRIPE…GRIPE


  30. Hello Jon, Greetings!!! Great post.. This really helped me to resolve one of my critical requirements… Thank you so much… Cheers @Srinivas

  31. I’d recommend to define the trigger as:

    CREATE OR REPLACE TRIGGER trig_test
    BEFORE INSERT ON test
    FOR EACH ROW
    WHEN(new.id IS NULL)
    BEGIN
    SELECT seq_test.nextval INTO :new.id FROM dual;
    END;

    This way you are able to specify the primary key (column ‘id’ in my example) if you need to.

  32. Hi,

    I am using sage crm in the frontend and oracle in the backend, we are using sage crm to log cases from the customer, whenever a new case is created, a ref id is generated by using the procedure and this works fine.

    The problem occurs only when i create a new case and cancel it, ie; i am not saving the case, but when i create a new case again, i get a new ref id instead of previous ref id. This is destroying my report.

    If i create a new case and cancel it then my ref id should be free and must be fetched for the next new case, how can i resolve this problem.

    help greatly appriciated……

    TIA
    Mohammed.

  33. Just a minor suggestion. This does not work the same as the MySQL solution (which allows you to specify a value for ID if you want to), as the trigger will always add the value from the sequence.

    If you wish to replicate that behaviour just change the line:

    SELECT test_sequence.nextval INTO :NEW.ID FROM dual;

    to be:

    IF :NEW.ID is NULL THEN
    SELECT test_sequence.nextval INTO :NEW.ID FROM dual;
    END IF;

    This will now only use the sequence if no value is passed in for ID.

  34. hey,wot do i do, if i need to generate a number sequence with character prefixes,say I need to auto increment the column ‘Product_id’ for every product inserted??

  35. S.

    If you want to have the same character appear before each sequence number all you need to do is concatenate the letter in the trigger like this:

    SELECT ‘A’ || test_sequence.nextval INTO :NEW.ID FROM dual;

    If you’re looking to increment the leading letter you’re going to have to get a bit trickier. Maybe others will have ideas, but something like a switch may be in order.

    Hope this helps.

Leave a Reply

Your email address will not be published. Required fields are marked *