What is an SQL Sequence?
Sequence is a feature supported by some database systems to produce unique values on demand. Some DBMS like MySQL supports
AUTO_INCREMENT in place of Sequence.
AUTO_INCREMENT is applied on columns, it automatically increments the column value by
1 each time a new record is inserted into the table.
Sequence is also some what similar to
AUTO_INCREMENT but it has some additional features too.
Creating a Sequence
Syntax to create a sequence is,
CREATE SEQUENCE sequence-name
START WITH initial-value
INCREMENT BY increment-value
CYCLE | NOCYCLE;
- The initial-value specifies the starting value for the Sequence.
- The increment-value is the value by which sequence will be incremented.
- The maximum-value specifies the upper limit or the maximum value upto which sequence will increment itself.
- The keyword
CYCLE specifies that if the maximum value exceeds the set limit, sequence will restart its cycle from the begining.
NO CYCLE specifies that if sequence exceeds
MAXVALUE value, an error will be thrown.
Using Sequence in SQL Query
Let's start by creating a sequence, which will start from
1, increment by
1 with a maximum value of
CREATE SEQUENCE seq_1
START WITH 1
INCREMENT BY 1
Now let's use the sequence that we just created above.
Below we have a class table,
The SQL query will be,
INSERT INTO class VALUE(seq_1.nextval, 'anu');
Resultset table will look like,
Once you use
nextval the sequence will increment even if you don't Insert any record into the table.