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

CREATE SEQUENCE [IF NOT EXISTS] [ owner. ] sequence [ { | INCREMENTBYstep | STARTWITH origin | { MAXVALUEmax | NOMAXVALUE } | { MINVALUEmin | NOMINVALUE } | { NOCYCLE | CYCLE } | { CACHEsize | NOCACHE } | { NOORDER | ORDER } } (explicit id ) ]
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; Informix® 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
1 Each keyword option can appear no more than once.