Sequence Definition

As of 2003 the SQL standard defines SEQUENCE generators. They are of particular use in auto-generating integer (surrogate key) values.

sequence_commands:
     CREATE SEQUENCE [ schema_name '.'] sequence_name [ seq_params ]
   | ALTER  SEQUENCE [ schema_name '.'] sequence_name [ alter_seq_params ]
   | DROP   SEQUENCE [ schema_name '.'] sequence_name

seq_params:
    [ AS seq_int_datatype ]
    [ START WITH bigint ]
    [ INCREMENT BY bigint ]
    [ MINVALUE bigint | NO MINVALUE ]
    [ MAXVALUE bigint | NO MAXVALUE ]
    [ CACHE bigint ]
    [ [ NO ] CYCLE ]

alter_seq_params:
    [ AS seq_int_datatype ]
    [ RESTART [ WITH { bigint | select_with_parens } ] ]
    [ INCREMENT BY bigint ]
    [ MINVALUE bigint | NO MINVALUE ]
    [ MAXVALUE bigint | NO MAXVALUE ]
    [ CACHE bigint ]
    [ [ NO ] CYCLE ]

seq_int_datatype:
     BIGINT
   | INTEGER
   | INT
   | SMALLINT
   | TINYINT

get_sequence_value:
     sys.get_value_for(schema_name, sequence_name)

increment_sequence_value:
     NEXT VALUE FOR [ schema_name '.'] sequence_name
   | sys.next_value_for(schema_name, sequence_name)

The sequence name must be unique within the schema.

The following defaults are used for the optional seq_params when not explicitly set:
AS BIGINT START WITH 1 INCREMENT BY 1 MINVALUE 0 MAXVALUE 9223372036854775807 CACHE 1 NO CYCLE

The NEXT VALUE FOR statement will be mapped internally to function sys.next_value_for(schema_name, sequence_name). It returns the current start value and increments it with the INCREMENT BY value of the sequence. This statement or function can be used anywhere a value expression is allowed.

Note that sequences internally always use a bigint for keeping track of the current sequence value.

Warning the AS seq_int_datatype clause is silently ignored and lost when dumping the CREATE SEQUENCE definition via msqldump or mclient program.

A sequence can only be dropped when all references to the sequence (e.g. in the DEFAULT specification of a column) have previously been removed. See example below.

Associated system table: sys.sequences

Associated information_schema view: information_schema.sequences

Example.

CREATE SEQUENCE "my_test_seq" AS integer START WITH 2 INCREMENT BY 2;
CREATE TABLE test (t integer DEFAULT NEXT VALUE FOR "my_test_seq", v varchar(10));
INSERT INTO test(v) VALUES ('ab');
INSERT INTO test(v) VALUES ('cd');
SELECT * FROM test;
+------+------+
| t    | v    |
+======+======+
|    2 | ab   |
|    4 | cd   |
+------+------+

SELECT * FROM sys.sequences;
+------+-----------+-------------+-------+----------+---------------------+-----------+----------+-------+
| id   | schema_id | name        | start | minvalue | maxvalue            | increment | cacheinc | cycle |
+======+===========+=============+=======+==========+=====================+===========+==========+=======+
| 7936 |      2000 | my_test_seq |     2 |        0 | 9223372036854775807 |         2 |        1 | false |
+------+-----------+-------------+-------+----------+---------------------+-----------+----------+-------+

SELECT id, number, name, type, type_digits, table_id, "default", "null" FROM sys.columns
 WHERE table_id in (select id from sys.tables where name = 'test');
+------+--------+------+---------+-------------+----------+------------------------------------+-------+
| id   | number | name | type    | type_digits | table_id | default                            | null  |
+======+========+======+=========+=============+==========+====================================+=======+
| 7938 |      0 | t    | int     |          31 |     7940 | next value for "sys"."my_test_seq" | true  |
| 7939 |      1 | v    | varchar |          10 |     7940 | null                               | true  |
+------+--------+------+---------+-------------+----------+------------------------------------+-------+


INSERT INTO test(t,v) VALUES (10, 'ee');	-- here the DEFAULT is NOT applied as a specific value for column t is specified
SELECT * FROM test;
+------+------+
| t    | v    |
+======+======+
|    2 | ab   |
|    4 | cd   |
|   10 | ee   |
+------+------+

-- adjust the sequence RESTART value using a SELECT query
ALTER SEQUENCE "my_test_seq" RESTART WITH (SELECT MAX(t) + 1 FROM test);
SELECT * FROM sys.sequences;
+------+-----------+-------------+-------+----------+---------------------+-----------+----------+-------+
| id   | schema_id | name        | start | minvalue | maxvalue            | increment | cacheinc | cycle |
+======+===========+=============+=======+==========+=====================+===========+==========+=======+
| 7936 |      2000 | my_test_seq |    11 |        0 | 9223372036854775807 |         2 |        1 | false |
+------+-----------+-------------+-------+----------+---------------------+-----------+----------+-------+

INSERT INTO test(v) VALUES ('ff');
SELECT * FROM test;
+------+------+
| t    | v    |
+======+======+
|    2 | ab   |
|    4 | cd   |
|   10 | ee   |
|   11 | ff   |
+------+------+

DROP SEQUENCE "my_test_seq";
-- Error: DROP SEQUENCE: unable to drop sequence my_test_seq (there are database objects which depend on it)

-- search for the dependent objects
select * from dependencies_vw where obj_type = 'sequence' and name = 'my_test_seq';
+------+----------+-------------+------------+------------------+--------------+-------------+----------------------+
| id   | obj_type | name        | used_by_id | used_by_obj_type | used_by_name | depend_type | dependency_type_name |
+======+==========+=============+============+==================+==============+=============+======================+
| 7936 | sequence | my_test_seq |       7940 | table            | test         |           7 | FUNCTION             |
+------+----------+-------------+------------+------------------+--------------+-------------+----------------------+

ALTER TABLE "test" ALTER COLUMN "t" DROP DEFAULT;

DROP TABLE "test";

DROP SEQUENCE "my_test_seq";

The functions sys.get_value_for(schema_name, sequence_name) and sys.next_value_for(schema_name, sequence_name) can be used to query the current value of a sequence. The difference is that sys.next_value_for(...) also advances the current value of a sequence to the next value.

The following queries demonstrate how these functions work:

CREATE SEQUENCE "myseq" AS INTEGER;
SELECT get_value_for('sys', 'myseq') as seqval;
+--------+
| seqval |
+========+
|      1 |
+--------+
SELECT next_value_for('sys', 'myseq') as seqval;
+--------+
| seqval |
+========+
|      1 |
+--------+
SELECT NEXT VALUE FOR myseq as seqval;
+--------+
| seqval |
+========+
|      2 |
+--------+
SELECT get_value_for('sys', 'myseq') as seqval;
+--------+
| seqval |
+========+
|      3 |
+--------+
DROP SEQUENCE "myseq";