Increasing primary key values in database

[Aileen_L]

Hi everyone !
I am creating a table with some constraint and the primary key constraint is also included in the table but I have some problem regarding implementation.Can any one tell me about the auto increment behavior of primary key value. How it would be performed. I need to increase the values of primary key with every record. Please help me for implementation of this table record.

[Praetor]

Increasing the value of Primary Key :

You can increase the values of primary key column or any column which you want to increase whenever you required.The oracle database provides a technique to solve this issue.

The Sequence, is the object of database which can perform this task using related parameter.Using CREATE SEQUENCE statement,create a sequence and implement it with your table column.

[Katty]

Creating sequence :

The CREATE SEQUENCE statement would be used to create the sequence object.Follow the below statement :

CREATE SEQUENCE product_seq
 START WITH     1000
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

Where START WITH is used for first sequence number to be generated.
The INCREMENT BY option Specify the interval between sequence numbers which can be negative and positive both.
NOCACHE is used to specify how many sequence values can be stored in the cache memory.
NOCYCLE is used to indicate that the sequence cannot generate more values after it higher and lower value which is default behavior.

[kelfro]

Implementing sequence :

After creation of sequence in database, you can implement it into your statement . I am going to show you an example which will increase the value of student_id column which is the primary key of Student_info table.The sequence has been created named seq_Student_id" and would be
used as follows :

INSERT INTO Student_info
(student_id, Student_name, Address)
VALUES
(seq_Student_id.NEXTVAL, 'Wilson', 'Washington........');

[Zecho]

Configuring the values of sequence :

You can configure the sequence behavior means different values that you have set up for particular options before,you can edit it according to your need using below statement :

ALTER SEQUENCE <Sequence_name> <Option_name> <value>;

Where Sequence_name is the name of sequence, Option_name is the name of option i.e.INCREMENT BY and the last is the value,set the value whatever you want

Dropping sequence :

You can drop the sequence using DROP statement :

DROP SEQUENCE <sequence_name>;