CREATE SEQUENCE statement

The CREATE SEQUENCE statement defines a sequence.

CREATE SEQUENCE schema.sequenceName

This option specifies the fully qualified name of the sequence.

TYPESCHEMA dataTypeSchema

This option specifies the schema for the sequence data type.

TYPENAME dataTypeName

This option specifies the name of the sequence data type.

OWNER ownerID

This option specifies the owner of the sequence.

dataType

This option specifies the data type of the sequence. The data type can be SMALLINT, INTEGER, BIGINT, DECIMAL, or a user-defined distinct type. The default is INTEGER.

LENGTH numeric

For sequences that are defined as a DECIMAL data type, this option specifies the length of the value.

START numeric

This option specifies the starting value for the sequence. The default value is the minimum value for an ascending sequence or the maximum value for a descending sequence.

INCREMENT numeric

This option specifies the amount of change in the values for the sequence. The value can be any positive or negative integer, including 0 (zero). The default value is 1.

MINVALUE

This option specifies the minimum value for a sequence. The minimum value can be equal to or less than the maximum value.

MINVALUE parameter values

Value	Description
Numeric	Specifies the minimum value.
<DEFLT>	Specifies a default value for the minimum value. If the sequence is descending, <DEFLT> is the smallest value allowed. If the sequence is ascending, the value is either the starting value or 1 if the starting value is not specified. <DEFLT> is the equivalent of NOMINVALUE.

MAXVALUE

This option specifies the maximum value for a sequence. The maximum value can be equal to or greater than the minimum value.

MAXVALUE parameter values

Value	Description
Numeric	Specifies the maximum value.
<DEFLT>	Specifies a default value for the maximum value. If the sequence is ascending, <DEFLT> is the largest value allowed. If the column is descending, the default value is either the starting value or -1 if the starting value is not specified. <DEFLT> is the equivalent of NOMAXVALUE.

CYCLE

This option indicates whether values should be generated after the maximum or minimum value is reached.

CYCLE parameter values

Value	Description
NO	Indicates that values should not be generated after the maximum or minimum value is reached. CYCLE NO is the default.
YES	Indicates that values should be generated after the maximum or minimum value is reached.

CACHE integer

This option specifies the number of a set of values that are stored for the sequence. A value of 0 (zero) is the equivalent of NO CACHE.

ORDER

This option indicates whether values must be generated in the order in which they are requested.

ORDER parameter values

Value	Description
NO	Indicates that values should not be generated in the order in which they are requested. ORDER NO is the default.
YES	Indicates that values must be generated in the order in which they are requested.

COMMENT 'string'

This option specifies the comment for the sequence.

CREATE SEQUENCE statement

On this page