Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    8

    Question Unanswered: How to add an automatic Primary Key

    Hi,
    I would like to create a table with a primary key that is not a field in the rows and that is generated automaticaly.
    I know is a silly question, but not knowing how this is called Im finding trouble to find it on the web.
    Any help wellcome,

    Thanks,

    Maria Eugenia

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Not sure I understand.

    A primary key always concerns one or more non-null columns in your table. I never heard of primary keys on "fields" that are not in the "row".

    Probably you would like to have a (numerical) column in your table that is populated automatically (with a sequential number) whenever you insert a new row ? Is that what you want ?

    CVM.

  3. #3
    Join Date
    Oct 2003
    Posts
    8
    Thats what I meant. To populate it automatically whenever you insert a new row.
    Sorry for my english, or lack of database knowledge.
    [

    QUOTE]Originally posted by cvandemaele
    Not sure I understand.

    A primary key always concerns one or more non-null columns in your table. I never heard of primary keys on "fields" that are not in the "row".

    Probably you would like to have a (numerical) column in your table that is populated automatically (with a sequential number) whenever you insert a new row ? Is that what you want ?

    CVM.
    [/QUOTE]

  4. #4
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    In other (than Oracle) databases, this feature is called IDENTITY (e.g. MSSQL).

    In Oracle, one solution I know involve using "sequences". In short, sequences are a special kind of objects that give numbers whenever they are asked for.

    Briefly :

    a) Create the sequence :
    CREATE SEQUENCE your_sequence START WITH your_starting_value [other options like INCREMENT];

    b) Create a BEFORE INSERT trigger (for each row) on your table that gets the next sequence number, and affects it to your primary key (column).

    CVM.

  5. #5
    Join Date
    Oct 2003
    Posts
    8
    Thaks,
    I did the following, but it gaves me a compiling error, that does not especifie which.

    CREATE SEQUENCE ref_number
    START WITH 1
    INCREMENT BY 1 ;

    create table invoice (
    customerID VARCHAR2(10) not null,
    invoiceAmount VARCHAR(12),
    constraint pk_customerID primary key ( customerID));

    create or replace trigger insertinvoice
    before insert on invoice
    for each row
    begin
    set customerID = ref_number;
    end;
    /

    Any suggestions??

    Thanks

  6. #6
    Join Date
    Sep 2003
    Location
    Brussel
    Posts
    52
    It's not necessary to create a before insert trigger. Just put in your insert statement [sequence_name].nextval and you get the next value from the sequence. If you want to know wich is the next value you can also do a SELECT [sequence_name].nextval FROM dual;

    Good luck!
    A good programmer is a LAZY programmer!

  7. #7
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Originally posted by Lazy
    It's not necessary to create a before insert trigger. Just put in your insert statement [sequence_name].nextval and you get the next value from the sequence. If you want to know wich is the next value you can also do a SELECT [sequence_name].nextval FROM dual;

    Good luck!
    Indeed you can call your sequence directly from within the INSERT statement, but I thought this was not your initial objective.

    What you should do :
    1. Redesign your table, and make CustomerID a NUMERIC column. This is not strictly necessary, but it avoids having to cast the result of the sequence-call.

    2. Your trigger is just fine, you should change only 1 row
    create or replace trigger insertinvoice
    before insert on invoice
    for each row
    begin
    --set customerID = ref_number; (OLD LINE, REPLACE WITH NEXT)
    select ref_number.nextval into :new.CustomerId from dual;
    end;
    /

    3. Now you can issue the following statement :
    insert into Invoice (InvoiceAmount) values ('123');

    You will see that Oracle assigned a value to your primary key.

    Good luck.

  8. #8
    Join Date
    Oct 2003
    Posts
    22

    Re: How to add an automatic Primary Key

    U can do one thing.

    Create the table as it is.

    create a function to generate next value in a sequence.

    create or replace function fun_name return data_type is
    variable1 data_type
    select max(primary_key_column) into variable1
    from table;
    return(variable1 + 1);
    end;

    Use TOCHAR or TONUMBER to synchronize the return clause with the return data_type.

    use it in the insert statement

    INSERT INTO table
    VALUES (fun_name,...);

    every time the insert is executed, the new row will contain a number greater than the greatest - primary key.

    u dont have to take care of the input value.

    also this function wont disturb sequence in the base table if accessed by others.

Posting Permissions

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