DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Auto Incrementing?

  1. #1
    Join Date
    Aug 2003
    Posts
    1

    Auto Incrementing?

    I'm trying to move from postgre to oracle 9i eventually so I need a little help converting stuff. Is there a way to create a table column to be auto incrementing by just setting the column type and then oracle will create the sequences on it's own?

    In PostgeSQL I can simply write...

    create table test(id SERIAL, primary key(id));

    and that will create the sequences for the id column and make sure it is a primary key.

    How would I do so in Oracle?

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Unfortunately there is no such data type in Oracle. You can make use of sequences ot write an INSERT trigger to insert a incremental number for that column whenever a new row is inserted.

    HTH
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  3. #3
    Join Date
    Aug 2003
    Location
    china
    Posts
    7
    a sequences or trigger is needed for the auto increase column

  4. #4
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    This will sort you out:

    http://www.oracle-base.com/Articles/8i/AutoNumber.asp

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  5. #5
    Join Date
    Jul 2003
    Location
    Sofia, Bulgaria
    Posts
    91
    CREATE OR REPLACE TRIGGER dept_bir
    BEFORE INSERT ON departments
    REFERENCING OLD AS old NEW AS new
    FOR EACH ROW
    BEGIN
    IF :new.id IS NULL THEN
    SELECT dept_seq.NEXTVAL
    INTO :new.id
    FROM dual;
    END IF;
    END;
    /

    Tim wouldn't it be a little faster that way:

    CREATE OR REPLACE TRIGGER dept_bir
    BEFORE INSERT
    ON departments
    FOR EACH ROW
    WHEN (:new.id IS NULL)
    BEGIN
    SELECT dept_seq.NEXTVAL
    INTO :new.id
    FROM dual;
    END;
    /

    Just a little bit

  6. #6
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Yep. Makes sense. No need to do the work unless you need to.

    Alternatively you could do the following but it's still unnecessary if you use your method:

    SELECT NVL(:new.id, dept_seq.NEXTVAL)
    INTO :new.id
    FROM dual;

    One thing to consider, by default some engines do not allow you to assign your own id, so you may want to do the following to mimic that type of system:

    SELECT dept_seq.NEXTVAL
    INTO :new.id
    FROM dual;

    Or:

    IF :new.id IS NOT NULL THEN
    RAISE_APPLICATION_ERROR(-20000, 'ID cannot be specified.');
    ELSE
    SELECT dept_seq.NEXTVAL
    INTO :new.id
    FROM dual;
    END IF;

    I've modified the article to reflect your comments and these suggestions.

    Many ways to skin a cat

    Cheers
    Last edited by TimHall; 08-22-2003 at 10:19 AM.
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width