#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    21
    Rep Power
    0

    Sequence syntax question...


    Hi,
    I'm trying to create a sequence for a primary key to simply auto-increment by the default of 1.
    I have a sql script written to generate mt tables, and I'm not sure how to modify the script to include the sequence. I also just want the sequence for a specific column, ie, PK, not the PK in all tables.

    Here's a snippet from my script:

    Code:
    create table image
    (
    image_id                int NOT NULL,
    source_id               int NOT NULL,
    CONSTRAINT image_id_pk PRIMARY KEY (image_id),
    CONSTRAINT fk_source_id FOREIGN KEY (source_id) REFERENCES source(source_id)
    );
    Would I add the create sequence statement right after the create table, and if so, how do I apply the sequence to only 1 table and a single column?

    Any help is appreciated,
    -mike
  2. #2
  3. No Profile Picture
    ......@.........
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2004
    Posts
    1,345
    Rep Power
    56
    Use NUMBER(15) or something larger for a sequence.

    create a sequence which is an object like a table.
    Code:
    CREATE SEQUENCE MYVALUES;
    NB: sequences DO NOT always run consecutively, because users may play with them.
    Or delete records from the table, for example.


    You put the sequence number in the table when inserting using the NEXTVAL procedure
    Code:
    INSERT INTO image
    (image_id,
    source_id ) 
    VALUES
    ( MYVALUES.NEXTVAL,
      123456
    );
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,987
    Rep Power
    375
    create sequence "whatever" start with 1 increment by 1;
    if you dont want all your sequences to start with 1, you can change it to 50, or 100 or whatever!
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    1
    Rep Power
    0

    Enforce insert using sequence only


    Hello.
    Q) how to enforce that a specific sequence has to be used for an insert into a table. No other value should be used.

    Thanks,
  8. #5
  9. Humble Learner
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2007
    Location
    Bangalore, India
    Posts
    280
    Rep Power
    120
    Originally Posted by sushantdd
    Hello.
    Q) how to enforce that a specific sequence has to be used for an insert into a table. No other value should be used.

    Thanks,
    You can't enforce that unless all the insert statements are executed through some procedure / package only. You can't restrict normal insert into the target table through some raw SQL.
    Last edited by debasisdas; August 30th, 2012 at 04:28 AM.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    Originally Posted by sushantdd
    Hello.
    Q) how to enforce that a specific sequence has to be used for an insert into a table. No other value should be used.

    Thanks,
    You will need to create an INSERT trigger which fills the column's value from the sequence.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

IMN logo majestic logo threadwatch logo seochat tools logo