|
Creates a sequence that can be used to generate primary key values that are unique across multiple tables, and for generating default values for a table.
CREATE [ OR REPLACE ] SEQUENCE [ owner.] sequence-name [ INCREMENT BY signed-integer ] [ START WITH signed-integer ] [ MINVALUE signed-integer | NO MINVALUE ] [ MAXVALUE signed-integer | NO MAXVALUE ] [ CACHE integer | NO CACHE ] [ CYCLE | NO CYCLE ]
Specifying OR REPLACE creates a new sequence, or replaces an existing sequence with the same name. If you do not use the OR REPLACE clause, an error is returned if you specify the name of a sequence that already exists for the current user.
Defines the amount the next sequence value is incremented from the last value assigned. The default is 1. Specify a negative value to generate a descending sequence. An error is returned if the INCREMENT BY value is 0.
Defines the starting sequence value. If you do not specify a value for the START WITH clause, MINVALUE is used for ascending sequences and MAXVALUE is used for descending sequences. An error is returned if the START WITH value is beyond the range specified by MINVALUE or MAXVALUE.
Defines the smallest value generated by the sequence. The default is 1. An error is returned if MINVALUE is greater than ( 2^63-1) or less than -(2^63-1). An error is also returned if MINVALUE is greater than MAXVALUE.
Defines the largest value generated by the sequence. The default is 2^63-1. An error is returned if MAXVALUE is greater than 2^63-1 or less than -(2^63-1).
Specifies the number of preallocated sequence values that are kept in memory for faster access. When the cache is exhausted, the sequence cache is repopulated and a corresponding entry is written to the transaction log. At checkpoint time, the current value of the cache is forwarded to the ISYSSEQUENCE system table. The default is 100.
Specifies whether values should continue to be generated after the maximum or minimum value is reached.
The default is NO CYCLE, which returns an error once the maximum or minimum value is reached.
A sequence is a database object that allows the automatic generation of numeric values. A sequence is not bound to a specific or unique table column.
Sequences can generate values in one of the following ways:
You control the behavior when the sequence runs out of values using the CYCLE clause.
If a sequence is increasing and it exceeds the MAXVALUE, MINVALUE is used as the next sequence value if CYCLE is specified. If a sequence is decreasing and it falls below MINVALUE, MAXVALUE is used as the next sequence value if CYCLE is specified. If CYCLE is not specified, an error is returned.
Sequence values cannot be used with views or materialized view definitions.
You must have the CREATE ANY SEQUENCE or CREATE ANY OBJECT system privilege to create sequences.
To replace an existing sequence, you must have one of the following:
CREATE ANY SEQUENCE and DROP ANY SEQUENCE system privileges.
CREATE ANY OBJECT and DROP ANY OBJECT system privileges.
ALTER ANY OBJECT or ALTER ANY SEQUENCE system privileges.
None
Sequences comprise SQL Language Feature T176. The software does not allow optional specification of the sequence data type. This behavior can be achieved with a CAST when using the sequence.
In addition, the following are not in the standard:
The following example creates a sequence named Test that starts at 4, increments by 2, does not cycle, and caches 15 values at a time:
CREATE SEQUENCE Test START WITH 4 INCREMENT BY 2 NO MAXVALUE NO CYCLE CACHE 15;