How to Create Auto Increment Columns in Oracle


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

Source:

http://www.lifeaftercoffee.com/2006/02/17/how-to-create-auto-increment-columns-in-oracle/

About these ads

22 thoughts on “How to Create Auto Increment Columns in Oracle

  1. Hi keshab, glad to know it’s useful for you. I’ve put the original url of this article so that people know who should get credit…yes, not me…thanks for posting the comment anyway :)

  2. Hi folks,

    Is there any way to create auto increment column in oracle the same way like in mysql.

    CREATE TABLE test (
    TEST1 Number(10) NOT NULL auto_increment,
    TEST2 varchar2(50) default NULL,
    PRIMARY KEY (TEST1)
    );

    Is it necessary to create a seq. and a trigger in case of oracle??

    Cheers

    Satish

  3. can we define auto increment when we create a table. acutally i do not want to create trigger for it. i wants that when i create a table with create table statement then i can define autoincrement

  4. rakesh, unfortunately oracle doesn’t support autoincrement so you have to create trigger to implement autoincrement…at least for rel 8i. I’m not quite sure about other versions…

  5. if i want to write a trigger on a particular attribute of a table i.e. if and only if a column is updated in table 1 then table 2 should be updated. how can i write this code?
    is it valid?
    create or replace trigger mytrigger
    after insert on table1.column name…..

  6. Pingback: How To Create Auto Increment Columns In Oracle « Anand Mahuli's Source Codes

  7. Hey, you used to write wonderful, but the last few posts have

    been kinda boring… I miss your tremendous writings.

    Past several posts are just a little bit out of track!

    come on!

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

    I love the way the author of this article tries to downplay this (and many other things) very common things, totally missing from Oracle. “Gee, it’s only a few lines of extra code, sequences, and triggers”. Want to know how much code you have to add with other versions of SQL: One word: AUTO_INCREMENT

    Done.

  9. hi, i have 10record in my table i have deleted five records in table iam using autoincrement in ORACLE10g after deleting 5 records iam inserting one more record but that record is starting from 11…i want to start that record in 6…….please help me………
    create table data(rno number(10)not null,name varchar2(15)not null);………..in RNO i have used auto increment using sequence and trigger

  10. Pingback: Idm oam oim

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s