[mikestan21]

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:

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

[jim mcnamara]

Use NUMBER(15) or something larger for a sequence.

create a sequence which is an object like a table.

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

INSERT INTO image
(image_id,
source_id ) 
VALUES
( MYVALUES.NEXTVAL,
  123456
);

[paulh1983]

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!

[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,

[debasisdas]

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.

[shammat]

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.