Sequence syntax question...

Dev Shed Forums Sponsor:

March 14th, 2005, 06:05 PM

mikestan21

Registered User

Join Date: Aug 2003

Posts: 21

Time spent in forums: 1 h 52 m 7 sec
Reputation 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

March 15th, 2005, 02:29 PM

jim mcnamara

......@.........

Join Date: Jun 2004

Posts: 1,345

Time spent in forums: 1 Week 4 Days 38 m 38 sec
Reputation Power: 54

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
);

March 16th, 2005, 11:30 AM

paulh1983

Contributing User

Join Date: Dec 2004

Posts: 2,230

Time spent in forums: 2 Weeks 1 Day 10 h 21 m 35 sec
Reputation Power: 201

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!

August 29th, 2012, 08:54 PM

sushantdd

Registered User

Join Date: Aug 2012

Posts: 1

Time spent in forums: 4 m 27 sec
Reputation 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,

August 30th, 2012, 04:25 AM

debasisdas

Humble Learner

Join Date: Jul 2007

Location: Bangalore, India

Posts: 279

Folding Points: 1142847 Folding Title: Super Ultimate Folder - Level 3

Time spent in forums: 2 Months 3 Days 4 h 48 m 5 sec
Reputation Power: 118

Send a message via Google Talk to debasisdas

Quote:

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.

August 30th, 2012, 05:11 AM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,684

Time spent in forums: 1 Month 3 Weeks 3 Days 19 h 42 m 19 sec
Reputation Power: 284

Quote:

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

Sequence syntax question...

Developer Shed Advertisers and Affiliates