CREATE SEQUENCE statement

Use the CREATE SEQUENCE statement to create a sequence database object from which multiple users can generate unique integers.

This statement is an extension to the ANSI/ISO standard for SQL.

Syntax

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 or BIGINT 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

Usage

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; HCL OneDB™ returns an error if you attempt to invoke the built-in NEXTVAL or CURRVAL functions in any other context.

Generated values logically resemble the BIGSERIAL or 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.

If you include the optional IF NOT EXISTS keywords, the database server takes no action (rather than sending an exception to the application) if a sequence object of the specified name is already registered in the current database, or if the specified name is the identifier of a table, view, or synonym in the current database.

Example

The following example creates a sequence, inserts values from the sequence into the table, and selects all rows and columns from the table.

CREATE SEQUENCE seq_2 
   INCREMENT BY 1 START WITH 1 
   MAXVALUE 30 MINVALUE 0 
   NOCYCLE CACHE 10 ORDER; 

CREATE TABLE tab1 (col1 int, col2 int); 
INSERT INTO tab1 VALUES (0, 0); 

INSERT INTO tab1 (col1, col2) VALUES (seq_2.NEXTVAL, seq_2.NEXTVAL)

SELECT * FROM tab1;

       col1        col2

          0           0
          1           1