Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2002
    Posts
    2

    Unanswered: Autogenerate primary key

    Hello everyone,

    I am a newbie with database. And I would be most grateful if someone could help me out.

    I have a table, say "st_order" which has a field name,"id" as primary key. What I want to do, is to add a record to this table, even though at the time of addition, I don't have or know the id.
    My concern is how do I autogenerate this primary key. Is there a way to do so in Oracle?If yes, how?

    Thanks in advance

  2. #2
    Join Date
    Jul 2002
    Posts
    8
    Hi,


    I'm not shure if there a mechanism to autogenerate a number while inserting to a table with a primary key.
    But what you can do in every case is to use a sequence to generate a unique number.

    bye

  3. #3
    Join Date
    Jun 2002
    Posts
    7

    Autogenerated Key

    Hi!

    If you want an autogenerated key and can live with a numeric key, you can use a combination of SEQUENCE and TRIGGER to insert the number at the time of an INSERT command.

    i.e.:
    create sequence st_table_seq_1 start with 10000;

    create or replace
    trigger st_table_tr
    before insert on st_rable
    for each row
    declare num integer;
    begin
    select st_table_seq_1.nextval into num
    from dual;
    :new.id := num;
    end;
    /
    show errors;

    If you want the primary key to be of type char, change:
    :new.id := to_char(num,'FM00000');
    This way you could create non-numerical key too, like on with a leading character "K":

    :new.id := 'K' + to_char(num,'FM00000');

    or use a column of the inserted row:
    :new.id := :new.company_type + to_char(num,'FM00000');

    Just make the column ID long enougth ...

    Maybe you need to take "number" as the data type fro the variable "num".

    joachim

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    tamarahz is new to database, joachim, so the trigger is probably too confusing

    tamarahz, are you asking whether you can assign your own value to the primary key sometimes, and let an autonumber be assigned other times? if so, that's weird, and maybe you should rethink the primary key

    if all you want is an autonumber in oracle, look up CREATE SEQUENCE in the docs

    rudy
    http://rudy.ca/

  5. #5
    Join Date
    Jul 2002
    Posts
    2
    Hi,

    Thank you for your suggestions. I am sure it will be of great help.

    Rudy, what I want to do is to autonumber the primary key at the time of a first insertion in the database. This primary key will not be changed thereafter and will be unique.

    Thanks again.

  6. #6
    Join Date
    Jun 2007
    Posts
    1

    Red face autogenerate primary key

    Hello,
    this is for generating a number for new records right? could you please tell me how to insert the number for the records that already exist?

    Thanks,
    aveeno

    Quote Originally Posted by jthuex
    Hi!

    If you want an autogenerated key and can live with a numeric key, you can use a combination of SEQUENCE and TRIGGER to insert the number at the time of an INSERT command.

    i.e.:
    create sequence st_table_seq_1 start with 10000;

    create or replace
    trigger st_table_tr
    before insert on st_rable
    for each row
    declare num integer;
    begin
    select st_table_seq_1.nextval into num
    from dual;
    :new.id := num;
    end;
    /
    show errors;

    If you want the primary key to be of type char, change:
    :new.id := to_char(num,'FM00000');
    This way you could create non-numerical key too, like on with a leading character "K":

    :new.id := 'K' + to_char(num,'FM00000');

    or use a column of the inserted row:
    :new.id := :new.company_type + to_char(num,'FM00000');

    Just make the column ID long enougth ...

    Maybe you need to take "number" as the data type fro the variable "num".

    joachim

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    almost 5 full years old, this thread! a new record!

    aveeno, i'll bet your table doesn't resemble any other table in this thread, am i right?

    what are you trying to achieve? please show your table and column names
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

Posting Permissions

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