March 14th, 2005, 06:05 PM
-
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
-
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
-
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
-
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
-
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
-
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