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;
CYCLEspecifies that if the maximum value exceeds the set limit, sequence will restart its cycle from the begining.
NO CYCLEspecifies that if sequence exceeds
MAXVALUEvalue, 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
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,
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.