Create a sequence

In OceanBase Database, a sequence is an auto-increment sequence number generated in the database based on some rules. It is usually a set of numeric values (of the numeric data type). A sequence auto-increments and therefore is often used as a primary or unique key.

You can use the CREATE SEQUENCE statement to create a sequence. A sequence provides two pseudo-columns: CURRVAL and NEXTVAL, which return the current sequence value and the next sequence value respectively. Each reference to NEXTVAL will update the value of CURRVAL.

To execute this statement, you must have the CREATE privilege.

The SQL syntax is as follows:

CREATE SEQUENCE [IF NOT EXISTS] [database_name.]sequence_name
[MINVALUE int_value | NOMINVALUE]
[MAXVALUE int_value | NOMAXVALUE]
[START WITH int_value]
[INCREMENT BY int_value]
[CACHE int_value | NOCACHE]
[ORDER | NOORDER]
[CYCLE | NOCYCLE];

The parameters are described as follows:

• IF NOT EXISTS: If the sequence to be created already exists and IF NOT EXISTS is not specified, the system returns an error. This parameter is optional.

• database_name.: the database in which the sequence resides. This parameter is optional. If you do not specify database_name, the sequence is created in the current logon database by default.

• MINVALUE int_value | NOMINVALUE: the minimum value of the sequence. The value range is [-10²⁷, (10²⁷-1)].

  If NOMINVALUE is specified, the minimum value is 1 for an ascending sequence and -(10²⁷-1) for a descending sequence.

  If MINVALUE is not specified, NOMINVALUE takes effect by default.

• MAXVALUE int_value | NOMAXVALUE: the maximum value of the sequence. The value range is [(-10²⁷+1), (10²⁸-1)].

  If NOMAXVALUE is specified, the maximum value is (10²⁸-1) for an ascending sequence, and -1 for a descending sequence.

  If MAXVALUE is not specified, NOMAXVALUE takes effect by default.

• START WITH: the start value of the sequence. The value must be less than or equal to MAXVALUE, and greater than or equal to MINVALUE. If no start value is specified, the start value is the minimum value for an ascending sequence and the maximum value for a descending sequence.

• INCREMENT BY: the auto-increment step of the sequence. The value cannot be 0. If the value is positive, the sequence ascends. If the value is negative, the sequence descends. If this parameter is not specified, the default value 1 takes effect.

• CACHE: the number of sequence values that the database preallocates and keeps in memory. The default value is 20.

• ORDER | NOORDER: specifies whether the values of the sequence are generated sequentially. ORDER indicates sequential generation, and NOORDER indicates non-sequential generation. The default value is NOORDER.

• CYCLE | NOCYCLE: specifies whether the values of the sequence are cyclically generated. CYCLE indicates a cycling sequence, and NOCYCLE indicates a non-cycling sequence. The default value is NOCYCLE.

The following example creates the sequence seq1 and specify the auto-increment step to 2:

obclient> CREATE SEQUENCE seq1 START WITH 1 MINVALUE 1 MAXVALUE 10 INCREMENT BY 2 NOCYCLE NOORDER CACHE 30;

For more information about the CREATE SEQUENCE statement, see CREATE SEQUENCE.

References

For more information about operations on a sequence, see the following topics:

• Query sequences

• Reference a sequence

• Modify a sequence

• Drop a sequence