Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2003
    Posts
    7

    Unanswered: auto-increment column....

    I need some quick help. I need to create some tables in Oracle9i that I need help with. I need to have 1 column be a primary key that will be a number that automatically increments whenever a new row is added. For example, in MS Access, you can set a column data type to auto_number. This column is a not null value and it will autmatically insert an incremented value whenever you insert any rows. Any help in how to do this in Oracle? I have been looking into making a default value in a constraint for that column, but am getting lost.

    Any help appreciated!!!


    -DJ

  2. #2
    Join Date
    Oct 2003
    Posts
    71

    Re: auto-increment column....

    You can do it by using Sequence in Oracle.

    Originally posted by djtech2k
    I need some quick help. I need to create some tables in Oracle9i that I need help with. I need to have 1 column be a primary key that will be a number that automatically increments whenever a new row is added. For example, in MS Access, you can set a column data type to auto_number. This column is a not null value and it will autmatically insert an incremented value whenever you insert any rows. Any help in how to do this in Oracle? I have been looking into making a default value in a constraint for that column, but am getting lost.

    Any help appreciated!!!


    -DJ

  3. #3
    Join Date
    Nov 2002
    Posts
    833
    create a sequence for the pk_<table_name>

    create sequence sq_<table_name> minvalue 1; [optional increment, maxvalue ...]

    insert into <table_name> (pk_<table_name>) values (sq_<table_name>.nextval);

  4. #4
    Join Date
    Aug 2003
    Posts
    7
    thanks for the reply guys. Is there any other way to do it so that it is a little more automated. What I mean is so that this column would automatically be populated with an incremented number (+1 from the largest number in the table) any time a row was entered, without having to specify a seperate "insert" statement for that column?

  5. #5
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482
    Originally posted by djtech2k
    thanks for the reply guys. Is there any other way to do it so that it is a little more automated. What I mean is so that this column would automatically be populated with an incremented number (+1 from the largest number in the table) any time a row was entered, without having to specify a seperate "insert" statement for that column?

    http://www.dbforums.com/t972816.html

  6. #6
    Join Date
    Nov 2003
    Posts
    3
    U can use a trigger which fires before every insert. In the trigger body write the code to increment the column variable.
    Like
    select max(col) into n from table_name
    :new.col:=n+1



    Originally posted by djtech2k
    thanks for the reply guys. Is there any other way to do it so that it is a little more automated. What I mean is so that this column would automatically be populated with an incremented number (+1 from the largest number in the table) any time a row was entered, without having to specify a seperate "insert" statement for that column?

  7. #7
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    First you create Sequence (this is the "auto number")

    Code:
    CREATE SEQUENCE sequence_name
    INCREMENT BY 1
    START WITH 1
    MINVALUE 1
    Then you create a Before Trigger on your table

    Code:
    CREATE OR REPLACE TRIGGER trigger_name
    BEFORE INSERT ON table_name
    FOR EACH ROW
    DECLARE
    
       lv_seq   number;
    
    BEGIN
    	 
       Select sequence_name.NextVal
       Into lv_seq
       From Dual;	 
    	 
       :new.table_primary_key_column := lv_seq;
      
    EXCEPTION
       -- Error Handler 
    END;
    this will get called before your insert is executed, and determine the next value of the sequence.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Carloa is correct, but why declare variables where none are needed, and why add an exception handler? This will do:

    Code:
    CREATE OR REPLACE TRIGGER trigger_name
    BEFORE INSERT ON table_name
    FOR EACH ROW
    BEGIN
    	 
       Select sequence_name.NextVal
       Into :new.table_primary_key_column
       From Dual;	 
    
    END;

  9. #9
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    Yeah, I like that better, selecting directly into the :new.column.

    I Just have a habit of trapping exceptions so that we can return a user-friendly error instead of

    ora-99999 whatever the error was

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Sorry, I was nit-picking really - but I always think "less is more" when it comes to programming!

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    this is what I have used in the past.
    I needed a column with a sequence for when I loaded data through sqlloader since the file was all lines of text, but had no way to order the text.

    Similar to Andrew's code.

    PHP Code:
    drop sequence zero_seq2;

    create sequence zero_seq2;

    create or replace trigger xindex_sequence
        before insert on XINDEX
        
    for each row
            
    declare
                
    v_seq number;
            
    begin
                select zero_seq2
    .nextval into v_seq from dual;
            if 
    INSERTING then
                
    :new.seq_no := v_seq;
            
    end if;
    end;

    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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