automatically assigned record ID
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: automatically assigned record ID

  1. #1
    Join Date
    Mar 2001
    Posts
    20

    Unhappy

    I recently installed oracle 8i on a windows 98 platform and am now trying to establish the tables for a new database. First let me say that I am faily aware of the relational database concept but very new to oracle and putting the concept to work. Having said this:

    I want to establish a table that should have a primary key (lets say account_ID) that is established automatically by the database everytime a new record is created. everytime a new record is created I want the database to automatically assign a new unique account_ID that can be used to identify the associated record. how do I design and establish the table with oracle 8i navigator? I would like the account_ID to be a 6 digit number and would like to start the first record with 100000 (second record 100001).

    I have been trying to use the rowID (sounded right and the documentation doesn't shed much light on how to use it). When I try to input data into the table the column using type rowID says N/A. If I enter the remaining fields of theat record and try to insert a new record I get the following error
    ORA-01400: cannot insert NULL into (column with type rowID)

    Please help.
    Marc
    Share on Google+

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    The rowid is something that you can use for query and not for inserting in. This rowid gets assigned by oracle internally. The best way to establish your needs is use the SEQUENCE and then insert those sequence number for your primary key.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!

    Share on Google+

  3. #3
    Join Date
    Feb 2001
    Posts
    22
    You may use Oracle Sequence to get unique primary key.

    CREATE SEQUENCE UNIQUENUM START WITH 100000 INCREMENT BY 1 NOCACHE

    Then, you can use

    SELECT UNIQUENUM.NextVal from Dual to get latest sequence no

    or

    INSERT INTO SAMPLE_TABLE (ID, D1, D2) VALUES (UniqueNum.NextVal, 'DATA', 'DATAB');
    Share on Google+

  4. #4
    Join Date
    Oct 2000
    Posts
    25
    --First create a table according to your requirements. e.g.,

    CREATE TABLE Temp_Table
    (ID number(6),
    NAME varchar2(20));

    --Then create a SEQUENCE that generates sequentially generated numbers starting with 100000. e.g.,

    CREATE SEQUENCE SEQ_Temp_Table START WITH 100000 nocache
    /

    --Now create a FUNCTION which return the next value to be inserted into your table. e.g.,

    CREATE FUNCTION Temp_Table_NEXT_ID RETURN NUMBER IS
    X NUMBER;
    BEGIN
    SELECT SEQ_Temp_Table.NextVal INTO X FROM DUAL;
    RETURN X;
    END;
    /

    --Then INSERT your records. e.g.,

    INSERT INTO Temp_Table Values
    (Temp_Table_NEXT_ID , 'MYNAME');

    I think this should work.

    Hope this helps.
    Share on Google+

  5. #5
    Join Date
    Mar 2001
    Posts
    20
    thanks so much - that was the concept I was looking for. now I know how I establish a sequence object. what do I have to do to use the sequence with the table. I assume I establish the ID column as number type, size 6, scale 0, no default, primary key 1, no foreign key. Sorry for being such a beginner.

    Marc
    Share on Google+

  6. #6
    Join Date
    Mar 2001
    Posts
    20
    i was a bit late with my reply. how can I do this in oracle 8i navigator. if not which application do I use (sql plus?)

    thanks
    Marc
    Share on Google+

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    CREATE SEQUENCE PR_SEQ_COD_OFI
    INCREMENT BY 1
    START WITH 100000
    NOCYCLE
    NOCACHE
    ;


    INSERT INTO XYZ(COD_OFI,)
    SELECT PR_SEQ_COD_OFI.NEXTVAL
    FROM ABC
    ;

    is this what you are looking for?
    Share on Google+

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