CREATE SEQUENCE

>>-CREATE SEQUENCE--+----------+--sequence---------------------->
                    '-owner--.-'
 
>--+--------------------------------------+--------------------><
   | .-----------------------------.      |
   | V              .-BY-.         | (1)  |
   '---+-INCREMENT--+----+--step-+-+------'
       |        .-WITH-.         |
       +-START--+------+--origin-+
       | .-NOMAXVALUE----.       |
       +-+-MAXVALUE--max-+-------+
       | .-NOMINVALUE----.       |
       +-+-MINVALUE--min-+-------+
       | .-NOCYCLE-.             |
       +-+-CYCLE---+-------------+
       +-+-CACHE--size-+---------+
       | '-NOCACHE-----'         |
       | .-ORDER---.             |
       '-+-NOORDER-+-------------'

Notes:

Each keyword option can appear no more than once.

Element	Description	Restrictions	Syntax
max	Upper limit of values	Must be an integer > origin	Literal Number
min	Lower limit of values	Must be an integer less than origin	Literal Number
origin	First number in the sequence	Must be an integer in INT8 range	Literal Number
owner	Owner of sequence	Must be an authorization identifier	Owner Name
sequence	Name that you declare here for the new sequence	Must be unique among sequence, table, view, and synonym names	Identifier
size	Number of values that are preallocated in memory	Integer > 1, but < cardinality of a cycle (= \|(max - min)/step\|)	Literal Number
step	Interval between successive values	Nonzero integer in INT range	Literal Number

A sequence (sometimes called a sequence generator or sequence object) returns a monotonically ascending or descending series of unique integers, one at a time. The CREATE SEQUENCE statement defines a new sequence object, declares its identifier, and registers it in the syssequences system catalog table.

Authorized users of a sequence can request a new value by including the sequence.NEXTVAL expression in DML statements. The sequence.CURRVAL expression returns the current value of the specified sequence. NEXTVAL and CURRVAL expressions are valid only within SELECT, DELETE, INSERT, and UPDATE statements; Dynamic Server returns an error if you attempt to invoke the built-in NEXTVAL or CURRVAL functions in any other context.

Generated values logically resemble the SERIAL8 data type, but can be negative, and are unique within the sequence. Because the database server generates the values, sequences support a much higher level of concurrency than a serial column can. The values are independent of transactions; a generated value cannot be rolled back, even if the transaction in which it was generated fails.

You can use a sequence to generate primary key values automatically, using one sequence for many tables, or each table can have its own sequence.

CREATE SEQUENCE can specify the following characteristics of a sequence:

Initial value
Size and sign of the increment between values.
Maximum and minimum values
Whether the sequence recycles values after reaching its limit
How many values are preallocated in memory for rapid access

A database can support multiple sequences concurrently, but the name of a sequence (or in an ANSI-compliant database, the owner.sequence combination) must be unique within the current database among the names of tables, temporary tables, views, synonyms, and sequences.

An error occurs if you include contradictory options, such as specifying both the MINVALUE and NOMINVALUE options, or both CACHE and NOCACHE.

CREATE SEQUENCE

Syntax

Usage