Results 1 to 5 of 5

Thread: auto number??

  1. #1
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441

    Question Unanswered: auto number??

    Is there an oracle equivelent of an Access "auto number" field - i.e a number that is unique and automatically generated?

    Or do i have to write a trigger or something???

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: auto number??

    You have to create a SEQUENCE:

    CREATE SEQUENCE myseq;

    Then you can use the sequence in insert statements like:

    INSERT INTO t (id) VALUES ( myseq.NEXTVAL );

    Or of course you can create a trigger to set :NEW.id := myseq.NEXTVAL

  3. #3
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    yeah I was just looking squences. Would this be the correct syntax for a trigger???
    Code:
    CREATE TRIGGER mk_pk BEFORE INSERT ON blah
    FOR EACH ROW
    BEGIN
    :NEW.code_name := myseq.NEXTVAL ;
    END;
    assuming the trigger was created ok.

    also, if i just do
    Code:
    CREATE SEQUENCE myseq;
    what kind of squence will this give me? It doesn't really matter what numbers; but what datatype do the fields in the table (the primary key) need to be? INT?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, your trigger is correct.

    Sequences always return integer values - by default, starting from 1 and incrementing by 1 (though there may be gaps). You can modify the starting and increment values if you need to. The column in the table should just be a NUMBER, or you can specify NUMBER(size,0) if you like, as long as size is big enough.

  5. #5
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441

    Thumbs up

    I don't mind gaps...the users will just have to cope

    Thanks! Will give it all a try in the morning...

Posting Permissions

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