Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    3

    Question Unanswered: auto increment in oracle9

    I want to use an auto increasing number on a primary key, how do I do this in SQL Plus when creating the tables? or is there a better solution for this?

    to give you an idea what it's all about this is one of the tables:

    -- TABLE TICKETS
    CREATE TABLE TICKETS
    (
    TicketID PLS_NUMBER(10) NOT NULL,
    EventID PLS_NUMBER(10) NOT NULL,
    PromotorID PLS_NUMBER(10) NOT NULL,

    PRIMARY KEY(TicketID),
    FOREIGN KEY(EventID) REFERENCES Event(EventID),
    FOREIGN KEY(PromotorID) REFERENCES Promotor(PromotorID)
    );

    it is the TicketID in this case I want an auto increment on..

    thanx in advance

  2. #2
    Join Date
    May 2004
    Location
    Germany
    Posts
    25
    The best way on an Oracle server is to use a sequence which is used within a trigger.

    - Your tabledefiniton -

    create sequence seq_ticketid increment by 1 start with 1;

    CREATE OR REPLACE TRIGGER tri_ticketid BEFORE INSERT ON tickets
    FOR EACH ROW
    BEGIN
    IF :NEW.ticketid IS NULL THEN
    SELECT seq_ticketid.NEXTVAL
    INTO :NEW.ticketid
    FROM dual;
    END IF;
    END;
    /

  3. #3
    Join Date
    May 2004
    Posts
    3
    Cool, thanx heaps mate.
    works perfectly

Posting Permissions

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