1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Auto number on Primary Key

Discussion in 'SQL PL/SQL' started by OracleTechie, Feb 19, 2011.

  1. OracleTechie

    OracleTechie Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Hello All,

    I have set up a schema using a DDL script in Oracle 10g. I am linking this through to a Visual Basic 2008 fron end. The connection is fine.
    My database of is for a car hire company project.

    I need to know how to create an auto increment for a primary key field, like Access does.

    For example here is my table structure for my Rental_Payment table.

    -- TABLE RENTAL_PAYMENT

    CREATE TABLE RENTAL_PAYMENT (
    PAYMENTNO NUMBER NOT NULL,
    CARDNO NUMBER NOT NULL,
    AMOUNT NUMBER NOT NULL,
    CARD_TYPE CHAR(20 ) NOT NULL
    )
    /

    -- ADD KEYS FOR TABLE RENTAL_PAYMENT

    ALTER TABLE RENTAL_PAYMENT ADD CONSTRAINT KEY6 PRIMARY KEY ( PAYMENTNO )
    /


    So for example the first record would appear as:

    PAYMENTNO CARDNO AMOUNT CARD_TYPE
    ======== ===== ====== =======
    01

    I would then enter values and the next payment number would be 02 etc.

    PAYMENTNO CARDNO AMOUNT CARD_TYPE
    ======== ===== ====== =======
    01 123412 £40.00 VISA
    02

    Visual Basic will usually have a star (*) beside a new entry field so 02 will be
    * 02


    etc........

    Thankyou in advance,
    OracleTechie
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What you want requires a trigger and a sequence:

    Code (SQL):
     
    CREATE SEQUENCE myrental_seq START WITH 1 INCREMENT BY 1 nomaxvalue nocycle;
     
    CREATE OR REPLACE TRIGGER myrental_Pk_Trg
    BEFORE INSERT ON rental_payment
    FOR each ROW
    BEGIN
    SELECT lpad(to_char(myrental_seq.NEXTVAL), 2, '0')
    INTO :NEW.paymentno
    FROM dual;
    END;
    /
     
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Do you mean to say that, once you enter the details for One record , next sequence to be inserted automatically ?

    What is actually the requirement ? Don't you think it is crazy ?
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No, it isn't crazy, it's a standard technique used for years to autogenerate values. On the next insert the id is automatically populated by the sequence. There is no record with just an id value unless all other values in the record are NULL (intentionally).
     
  5. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    I have few queries..
    1) What is the exact requirement?

    2)

    Code (SQL):
    SELECT lpad(to_char(myrental_seq.NEXTVAL), 2, '0')
    INTO :NEW.paymentno
    FROM dual;
    In the above query,
    I dont understand is it possible to enter char values into number datatype column.
    why becuase paymentno was defined as number type...
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    By the example posted the id column would be declared as a varchar2 column:

    "I would then enter values and the next payment number would be 02 etc.

    PAYMENTNO CARDNO AMOUNT CARD_TYPE
    ======== ===== ====== =======
    01 123412 £40.00 VISA
    02
    "

    as a number column will not preserve leading 0's.
     
  7. OracleTechie

    OracleTechie Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Hello All,

    Thankyou for all the replies

    It does not matter about having 02 as the next ID. I'll start over.

    1. I need a sequence first of all. Just a standard increment of one.

    create sequence seq_paymentno;

    This is fine for the sequence creation (Correct me if I'm wrong).


    2. So as Zargon correctly pointed out, what I want is the next value of the sequence to appear in the payment number field, as an example 2, (does not matter about having a 0 padded as in '02', 2 is fine). I need this when adding a new record, as Oracle does not provide an auto number field like Access.

    I need a trigger that will insert a 2 in this field and leave all the other fields bland, so as I can enter the details for the new record.
    Then if I go to add a further record '3' will appear in the paymentno field and so on.

    On the VB form the add new record is part of the Binding Navigator and is denoted by a yellow plus sign.

    (Also can I ask why the dual table is mentioned, as part of the table? Is this not a dummy table?)

    Thanks in advance,
    OracleTechie
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This should give you all of the necessary pieces:

    Code (SQL):
     
    CREATE SEQUENCE myrental_seq START WITH 1 INCREMENT BY 1 nomaxvalue nocycle;
     
    CREATE OR REPLACE TRIGGER myrental_Pk_Trg
    BEFORE INSERT ON rental_payment
    FOR each ROW
    BEGIN
    SELECT myrental_seq.NEXTVAL
    INTO :NEW.paymentno
    FROM dual;
    END;
    /
     
    You want the NOMAXVALUE and NOCYCLE options to prevent any cycling of the sequence and to prevent errors upon reaching the maximum value allowed; read here:

    http://oratips-ddf.blogspot.com/2008/06/out-of-sequence.html

    Note the examples for cycling (repeating) sequences and reaching the maximum value for a sequence.
     
  9. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India

    The above trigger may not serve the purpose of OP. That trigger will assign the next sequence to the current record (cuurent payment No)

    Code (SQL):

    SQL> SELECT * FROM TAB_1;

    no ROWS selected

    SQL> CREATE OR REPLACE TRIGGER TAB_1_Trg
      2  BEFORE INSERT ON TAB_1
      3  FOR each ROW
      4  BEGIN
      5  SELECT REC_NUM_SEQ.NEXTVAL
      6  INTO :NEW.PO_ID
      7  FROM dual;
      8  END;
      9  /

    TRIGGER created.

    SQL> INSERT INTO TAB_1 (PN,RECEIVED) VALUES('A',1000);

    1 ROW created.

    SQL> SELECT * FROM TAB_1;

    PO_ID P   RECEIVED
    ----- - ----------
    83600 A       1000

    SQL> INSERT INTO TAB_1
      2  VALUES(100,'A',2000);

    1 ROW created.

    SQL> SELECT * FROM TAB_1;

    PO_ID P   RECEIVED
    ----- - ----------
    83600 A       1000
    83601 A       2000

    SQL>
     
    OP want to create the new record with next sequence. For taht you might have to create an update trigger as folows.

    Code (SQL):

    SQL> CREATE OR REPLACE TRIGGER TAB_Trg
      2  after UPDATE ON TAB_1
      3  DECLARE
      4  BEGIN
      5  INSERT INTO TAB_1 (PO_ID)
      6  SELECT REC_NUM_SEQ.NEXTVAL
      7  FROM dual;
      8  END;
      9  /

    TRIGGER created.

    SQL> SELECT * FROM TAB_1;

    PO_ID P   RECEIVED
    ----- - ----------
    83600 A       1000
    83601 A       2000
    83955 B       3000
    83956

    SQL> UPDATE TAB_1
      2  SET PN='B',
      3      RECEIVED=3500
      4  WHERE PO_ID=83956;

    1 ROW updated.

    SQL> SELECT * FROM TAB_1;

    PO_ID P   RECEIVED
    ----- - ----------
    83600 A       1000
    83601 A       2000
    83955 B       3000
    83956 B       3500
    83957

    SQL>
     
    But this option is not a foolproof one.
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    We don't know what the OP really wants because the description is not consistent. First he describes what my trigger will provide:

    "2. So as Zargon correctly pointed out, what I want is the next value of the sequence to appear in the payment number field, as an example 2, (does not matter about having a 0 padded as in '02', 2 is fine). I need this when adding a new record, as Oracle does not provide an auto number field like Access."

    Then he goes on and describes a different result:

    "I need a trigger that will insert a 2 in this field and leave all the other fields bland, so as I can enter the details for the new record."

    Depending on the interpretation both triggers satisfy the description (mine ONLY populates the PK so the remaining supplied values remain untouched and the record is correctly numbered and yours provides next record containing only the next PK value) yet mine is the 'norm' for setting up an autonumber generator in Oracle (at least in my 21 years of experience). You may be correct but I can't see the logic in assigning the next PK value to an empty record requiring an update of the table (a less efficient mechanism in many cases) rather than assigning the next PK value to the next set of data inserted into the table. Also your trigger won't populate the very first value of the PK as yours requires an update and, in an empty table, there is nothing to update.