Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    7

    Unanswered: Using sequence as default in Oracle table

    Hi All,
    I want to use a sequence as the primary key column in my oracle table.
    This can be done using an insert trigger...but i dont want to use trigger.

    Can it be done using a default constraint. If yes..pls let me know how to do it.


    Thanx,
    Divaker

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    You can create a Oracle sequence as

    CREATE SEQUENCE seq_name ... and use it into the insert statement.

    For more information goto

    http://www.techonthenet.com/oracle/sequences.htm
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Nov 2003
    Posts
    7
    Thanks Hings,
    Can it be done using default for the column.

    Amit

  4. #4
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    You you can do this with a trigger, something like this:

    Code:
    CREATE OR REPLACE TRIGGER your_trigger_name
     BEFORE INSERT ON your_table_name
      FOR EACH ROW 
      WHEN (your_column_name IS NULL)
      BEGIN
        SELECT your_sequence_name.NEXTVAL INTO :new.your_column_name FROM dual; 
      END;
    /
    When you insert values into the table without a value for your column it will be populated from the sequence.

  5. #5
    Join Date
    Aug 2012
    Posts
    2

    Smile

    To the best of my knowledge, the WHEN statement needs to have OLD or NEW prior to the column name. e.g OLD.Column_name

    When (OLD.Column_name is NULL)

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    YEA!
    Another newbie dregs up a 8+ year old thread to show off his knowledge.

    You never get a second chance to make a first impression!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Aug 2012
    Posts
    2
    I might be new to this site, but if I've made a mistake by providing information to anyone that may visit this link, then I apologise. I thought this forum was here to help people. As it was, the original post did not completely resolve my problem and I had to find the answer elsewhere. I thought it might be nice to save someone else the trouble if I posted what I found here.
    Unlike some, I don't need to prove my knowledge. I get enough satisfaction knowing I might have helped someone.

Posting Permissions

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