Create Sequence

Valid in: SQL, ESQL

The Create Sequence statement creates new sequences. Sequences are defined database entities that are used to supply a set of integer values to an application in sequential order according to a set of definition parameters. These parameters are set by specifying sequence_options as explained below.

Syntax

The Create Sequence statement has the following format:

[EXEC SQL] CREATE SEQUENCE [schema.]sequence_name [sequence_options]

• sequence options

  Define parameters that control how the sequence supplies data when requested by an application. Sequence options can be specified in any order, and none are required.

  Any of the following options can be specified in a blank-space separated list:

  • AS data type

    Specifies whether the data type is an integer or a decimal (with some precision and 0 scale).

    Default: INTEGER

  • START WITH number

    Specifies the start of the sequence as some integer constant. The default value is 1 for positive sequences (positive increment) and -1 for negative sequences (negative increment). (This option is valid with the CREATE SEQUENCE statement only.)

  • RESTART WITH number

    Specifies a new start value for the sequence. (This option is valid with the ALTER SEQUENCE statement only.)

  • INCREMENT BY number

    Specifies the increment value (positive or negative) that produces successive values of the sequence.

    Default: 1

  • MAXVALUE number

    Specifies the maximum value allowed for the sequence.

    Defaults: For positive integer sequences: 2**31-1

    For positive decimal(n) sequences: 10**(n+1)-1

    For negative sequences: -1

  • NO MAXVALUE / NOMAXVALUE

    Specifies that sequences can generate values with an upper bound equivalent to that of the data type chosen to hold the sequence (for example, 2**31-1 for integers).

  • MINVALUE number

    Specifies the minimum value allowed for the sequence.

    Default: For positive sequences: 1

    For negative integer sequences: -2**31

    For negative decimal(n) sequences: -(10**(n+1)-1)

  • NO MINVALUE/NOMINVALUE

    Specifies that sequences can generate values with a lower bound equivalent to that of the data type chosen to hold the sequence (for example, -2**31 for integers).

  • CACHE number

    Specifies the number of sequence values held in server memory. Once the supply of numbers is exhausted, Ingres requires a catalog access to acquire the next set.

    Default: 20

  • NO CACHE / NOCACHE

    Specifies that sequence values are not to be cached by the server. When this option is selected, a catalog access is required for each request for a sequence value. This can severely degrade application performance.

    Default: CACHE 20 (when neither CACHE nor NOCACHE are specified), which ensures that the catalog overhead will be reasonably small.

  • CYCLE

    Specifies that the sequence restarts at the beginning value once it reaches the minimum value (negative increment) or maximum value (positive increment).

    Default: NO CYCLE

  • NO CYCLE / NOCYCLE

    Specifies that the sequence is not cycled when the last valid value is generated. An error is issued to the requesting transaction.

  • ORDER
  • NO ORDER / NOORDER

    These options are included solely for syntax compatibility with other DBMSes that implement sequences, and are not currently supported in Ingres.

    Default: NOORDER