Syntax
------

CREATE [OR REPLACE] [TEMPORARY] SEQUENCE [IF NOT EXISTS] sequence_name
[ INCREMENT [ BY | = ] increment ]
[ MINVALUE [=] minvalue | NO MINVALUE | NOMINVALUE ]
[ MAXVALUE [=] maxvalue | NO MAXVALUE | NOMAXVALUE ]
[ START [ WITH | = ] start ] 
[ CACHE [=] cache | NOCACHE ] [ CYCLE | NOCYCLE] 
[table_options]
The options for CREATE SEQUENCE can be given in any order, optionally followed
by table_options.

table_options can be any of the normal table options in CREATE TABLE but the
most usable ones are ENGINE=... and COMMENT=.

NOMAXVALUE and NOMINVALUE are there to allow one to create SEQUENCEs using the
Oracle syntax.

Description
-----------

CREATE SEQUENCE will create a sequence that generates new values when called
with NEXT VALUE FOR sequence_name. It's an alternative to AUTO INCREMENT when
one wants to have more control of how the numbers are generated. As the
SEQUENCE caches values (up to CACHE) it can in some cases be much faster than
AUTO INCREMENT. Another benefit is that one can access the last value
generated by all used sequences, which solves one of the limitations with
LAST_INSERT_ID().

CREATE SEQUENCE requires the CREATE privilege.

DROP SEQUENCE can be used to drop a sequence, and ALTER SEQUENCE to change it.

Arguments to Create
-------------------

The following options may be used:

+---------------+------------------------------+----------------------------+
| Option        | Default value                | Description                |
+---------------+------------------------------+----------------------------+
| INCREMENT     | 1                            | Increment to use for       |
|               |                              | values. May be negative.   |
|               |                              | Setting an increment of 0  |
|               |                              | causes the sequence to     |
|               |                              | use the value of the       |
|               |                              | auto_increment_increment   |
|               |                              | system variable at the     |
|               |                              | time of creation, which    |
|               |                              | is always a positive       |
|               |                              | number. (see MDEV-16035).  |
+---------------+------------------------------+----------------------------+
| MINVALUE      | 1 if INCREMENT > 0 and       | Minimum value for the      |
|               | -9223372036854775807 if      | sequence                   |
|               | INCREMENT < 0                |                            |
+---------------+------------------------------+----------------------------+
| MAXVALUE      | 9223372036854775806 if       | Max value for sequence     |
|               | INCREMENT > 0 and -1 if      |                            |
|               | INCREMENT < 0                |                            |
+---------------+------------------------------+----------------------------+
| START         | MINVALUE if INCREMENT > 0    | First value that the       |
|               | and MAX_VALUE if INCREMENT<  | sequence will generate     |
|               | 0                            |                            |
+---------------+------------------------------+----------------------------+
| CACHE         | 1000                         | Number of values that      |
|               |                              | should be cached. 0 if no  |
|               |                              | CACHE.  The underlying     |
|               |                              | table will be updated      |
|               |                              | first time a new sequence  |
|               |                              | number is generated and    |
|               |                              | each time the cache runs   |
|               |                              | out.                       |
+---------------+------------------------------+----------------------------+

If CYCLE is used then the sequence should start again from MINVALUE after it
has run out of values. Default value is NOCYCLE.

Constraints on Create Arguments
-------------------------------

To be able to create a legal sequence, the following must hold:

* MAXVALUE >= start
* MAXVALUE > MINVALUE
* START >= MINVALUE
* MAXVALUE <= 9223372036854775806  (LONGLONG_MAX-1)
* MINVALUE >= -9223372036854775807 (LONGLONG_MIN+1)

Note that sequences can't generate the maximum/minimum 64 bit number because
of the constraint of MINVALUE and MAXVALUE.

Atomic DDL
----------

MariaDB starting with 10.6.1
----------------------------
MariaDB 10.6.1 supports Atomic DDL and CREATE SEQUENCE is atomic.

Examples
--------

CREATE SEQUENCE s START WITH 100 INCREMENT BY 10;

CREATE SEQUENCE s2 START WITH -100 INCREMENT BY -10;

The following statement fails, as the increment conflicts with the defaults

CREATE SEQUENCE s3 START WITH -100 INCREMENT BY 10;
ERROR 4082 (HY000): Sequence 'test.s3' values are conflicting

The sequence can be created by specifying workable minimum and maximum values:

CREATE SEQUENCE s3 START WITH -100 INCREMENT BY 10 MINVALUE=-100 MAXVALUE=1000;

URL: https://mariadb.com/kb/en/create-sequence/