CREATE SEQUENCE v16

Name

CREATE SEQUENCE — Define a new sequence generator.

Synopsis

CREATE SEQUENCE <name> [ INCREMENT BY <increment> ]
  [ { NOMINVALUE | MINVALUE <minvalue> } ]
  [ { NOMAXVALUE | MAXVALUE <maxvalue> } ]
  [ START WITH <start> ] [ CACHE <cache> | NOCACHE ] [ CYCLE ]

Description

CREATE SEQUENCE creates a sequence number generator. This process involves creating and initializing a special single-row table with the specified name. The generator is owned by the user who created it.

If you include a schema name, then the sequence is created in the specified schema. Otherwise it's created in the current schema. The sequence name differ from the name of any other sequence, table, index, or view in the same schema.

After you create a sequence, use the functions NEXTVAL and CURRVAL to operate on it. These functions are documented in SQL reference.

Parameters

name

The name (optionally schema-qualified) of the sequence to create.

increment

The optional clause INCREMENT BY increment specifies the value to add to the current sequence value to create a new value. Use a positive value to create ascending sequence and a negative value to create a descending sequence. The default value is 1.

NOMINVALUE | MINVALUE minvalue

The optional clause MINVALUE minvalue determines the minimum value a sequence can generate. The defaults are 1 for ascending and -2⁶³-1 for descending sequences. You can use the keywords NOMINVALUE to set this behavior to the default.

NOMAXVALUE | MAXVALUE maxvalue

The optional clause MAXVALUE maxvalue determines the maximum value for the sequence. The defaults are 2⁶³-1 for ascending and -1 for descending sequences. You can use the keywords NOMAXVALUE to set this behavior to the default.

start

The optional clause START WITH start allows the sequence to begin anywhere. The default starting value is minvalue for ascending sequences and maxvalue for descending ones.

cache

The optional clause CACHE cache specifies how many sequence numbers to preallocate and store in memory for faster access. The minimum value is 1, which is also the default. This setting generates only one value at a time, that is, NOCACHE.

CYCLE

The CYCLE option allows the sequence to wrap around when the maxvalue or minvalue is reached by an ascending or descending sequence, respectively. If the limit is reached, the next number generated is minvalue or maxvalue, respectively.

If you omit CYCLE, any calls to NEXTVAL after the sequence reaches its maximum value return an error. You can use the keywords NO CYCLE to use the default behavior. However, this term isn't compatible with Oracle databases.

Notes

Sequences are based on big integer arithmetic, so the range can't exceed the range of an eight-byte integer (-9223372036854775808 to 9223372036854775807). Some older platforms might not have compiler support for eight-byte integers. In this case, sequences use regular INTEGER arithmetic (range -2147483648 to +2147483647).

Unexpected results can occur if you use a cache setting greater than 1 for a sequence object to be used concurrently by multiple sessions. Each session allocates and caches successive sequence values during one access to the sequence object and increases the sequence object’s last value accordingly. Then, the next cache-1 that uses NEXTVAL in that session returns the preallocated values without touching the sequence object. So, any numbers allocated but not used in a session are lost when that session ends. "Holes" in the sequence result.

Furthermore, although multiple sessions are guaranteed to allocate distinct sequence values, the values might be generated out of sequence when all the sessions are considered. For example, with a cache setting of 10, session A might reserve values 1..10 and return NEXTVAL=1. Then session B might reserve values 11..20 and return NEXTVAL=11 before session A generates NEXTVAL=2. Thus, with a cache setting of 1, it's safe to assume that NEXTVAL values are generated sequentially. With a cache setting greater than 1, assume only that the NEXTVAL values are all distinct, not that they are generated sequentially. Also, the last value reflects the latest value reserved by any session, whether or not it was already returned by NEXTVAL.

Examples

Create an ascending sequence called serial, starting at 101:

CREATE SEQUENCE serial START WITH 101;

Select the next number from this sequence:

SELECT serial.NEXTVAL FROM DUAL;

Output

 nextval
---------
     101
(1 row)

Create a sequence called supplier_seq with the NOCACHE option:

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    NOCACHE;