CREATE SEQUENCE

This topic describes how to create different types of sequences.

New sequences

Syntax

CREATE [NEW] SEQUENCE <name>
[ START WITH <numeric value> ]
[ INCREMENT BY <numeric value> ]
[ MAXVALUE <numeric value> ]
[ CYCLE | NOCYCLE ]

Parameters

Parameter Description
START WITH The start value for the new sequence. If you do not configure this parameter, the default start value is used. Default value: 1.
INCREMENT BY The increment between two adjacent sequence values, also called the interval value or step size. If you do not configure this parameter, the default start value is used. Default value: 1.
MAXVALUE The maximum value for the new sequence. The value must be a positive integer. If you do not specify this parameter, the default maximum value is used. The default maximum value is 9223372036854775807 and of the signed BIGINT data type.
CYCLE or NOCYCLE You can select only one of the parameters. CYCLE: When the maximum value for the new sequence is reached, the start value can be used again in the new sequence. NOCYCLE: When the maximum value for the new sequence is reached, the start value cannot be used again in the new sequence. If more values are assigned, an error is returned. If you do not configure this parameter, the default value of NOCYCLE is used.

Note If you do not specify the sequence type when you create a sequence in a database that is in AUTO mode, a new sequence is created by default. You cannot create a new sequence in a database that is in DRDS mode. Examples

Create a new sequence in which the start value is 1000.

CREATE NEW SEQUENCE newseq START WITH 1000;

Create a new sequence in which the start value of 1, the step size is 2, the maximum value is 100, the start value cannot be used again.

CREATE NEW SEQUENCE newseq2 START WITH 1 INCREMENT BY 2 MAXVALUE 100 CYCLE;

Group sequences

Syntax

CREATE [GROUP] SEQUENCE <name>
[ START WITH <numeric value> ]
[ UNIT COUNT <numeric value> INDEX <numeric value> ]

Note If you do not specify the sequence type when you create a sequence in a database that is in DRDS mode, a group sequence is created by default. If you want to create a group sequence in a database that is in AUTO mode, you must specify GROUP as the sequence type. Parameters {#concept-2068722-entry-fp4-adx-5au}

Parameter Description
START WITH By default, the start value of a group sequence is determined based on the value of the UNIT COUNT parameter and the value of the INDEX parameter. If you do not configure the UNIT COUNT parameter and INDEX parameter, the default start value 100001 is used.
UNIT COUNT The number of units in the group sequence. Default value: 1.
INDEX The unit index of the unit sequence. Valid values range from 0 to the value that is obtained as the difference of the number of units minus 1. Default value: 0.

Note

  • A group sequence contains nonconsecutive values. The value of the START WITH parameter is specified only for reference. The actual start value of the group sequences may be greater than the value of the START WITH parameter in your business scenario.

  • A group sequence that contains multiple units cannot be converted to a sequence of another type.

  • After a group sequence is created, you cannot change the values of the UNIT COUNT parameter and INDEX parameter.

Examples

Create a group sequence that contains only one unit.

CREATE GROUP SEQUENCE groupseq;

Create unit sequences in three instances or databases to generate a global group sequence. The sequence name and the number of units that are specified in the definitions of the unit sequences are the same, and the index of each unit sequence is unique.

  • Create a unit sequence in Instance 1 or Database 1.

    CREATE GROUP SEQUENCE ugroupseq UNIT COUNT 3 INDEX 0;
    
  • Create a unit sequence in Instance 2 or Database 2.

    CREATE GROUP SEQUENCE ugroupseq UNIT COUNT 3 INDEX 1;
    
  • Create a unit sequence in Instance 3 or Database 3.

    CREATE GROUP SEQUENCE ugroupseq UNIT COUNT 3 INDEX 2;
    

Time sequences

Syntax

CREATE TIME SEQUENCE <name>

Important The column that is used to store the values of a time sequence must be of the BIGINT data type. Example

Create a time sequence.

CREATE TIME SEQUENCE seq3;

results matching ""

    No results matching ""