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.
Syntax to create a sequence is,
CREATE SEQUENCE sequence-name
START WITH initial-value
INCREMENT BY increment-value
MAXVALUE maximum-value
CYCLE | NOCYCLE;
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.Let's start by creating a sequence, which will start from 1
, increment by 1
with a maximum value of 999
.
CREATE SEQUENCE seq_1
START WITH 1
INCREMENT BY 1
MAXVALUE 999
CYCLE;
Now let's use the sequence that we just created above.
Below we have a class table,
ID | NAME |
---|---|
1 | abhi |
2 | adam |
4 | alex |
The SQL query will be,
INSERT INTO class VALUE(seq_1.nextval, 'anu');
Resultset table will look like,
ID | NAME |
---|---|
1 | abhi |
2 | adam |
4 | alex |
1 | anu |
Once you use nextval
the sequence will increment even if you don't Insert any record into the table.