Auto Incrementing Columns in Oracle

Unlike SQL server, MySQL etc. Oracle doesn’t have identity columns. This is a bit of a pain when you need an auto incrementing column for a unique ID, which I use all the time as a primary key in the “many” table of a “one to many” relationship. 

Oracle deals with this by using a sequence and a trigger attached to insert statements. This post from lifeaftercoffee describes the method I followed to get it to work:

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.

If you don’t want to use a trigger you can manually add the next value in the sequence during your insert statement:

insert into test values(test_sequence.nextval, ‘my data!’);

One thought on “Auto Incrementing Columns in Oracle

  1. Hello would you mind stating which blog platform you’re using?
    I’m going to start my own blog in the near future but I’m having a tough time selecting between BlogEngine/Wordpress/B2evolution and Drupal.
    The reason I ask is because your design seems different then most blogs and I’m looking
    for something unique. P.S Sorry for being off-topic but I had
    to ask!

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>