In OceanBase AI Database, a sequence is an auto-increment numeric sequence number generated by the database according to certain rules. It is usually a set of spaced values (of a numeric type). Due to its auto-increment property, a sequence is often used as a primary key 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 time NEXTVAL is queried, the value of CURRVAL is advanced.
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];
Instructions for using the statement:
To execute this statement, you must have the
CREATEprivilege.IF NOT EXISTS: Optional. If the sequence to be created already exists andIF NOT EXISTSis not specified, the system returns an error.database_name.: Optional. Specifies the database where the sequence resides. Ifdatabase_name.is not specified, the sequence is created in the current logged-in database by default.MINVALUE int_value | NOMINVALUE: Specifies the minimum value of the sequence. The value range is [-1027 ~ (1027-1)].If
NOMINVALUEis specified, the minimum value is 1 for an ascending sequence and -(1027-1) for a descending sequence.If
MINVALUEis not specified, the default isNOMINVALUE.MAXVALUE int_value | NOMAXVALUE: Specifies the maximum value of the sequence. The value range is [(-1027+1) ~ (1028-1)].If
NOMAXVALUEis specified, the maximum value is(1028-1)for an ascending sequence and-1for a descending sequence.If
MAXVALUEis not specified, the default isNOMAXVALUE.START WITH: Specifies the starting value of the sequence. This value must be less than or equal toMAXVALUEand greater than or equal toMINVALUE. If not specified, the default value is the minimum value for an ascending sequence and the maximum value for a descending sequence.INCREMENT BY: Specifies the increment step of the sequence. This value cannot be0. If specified as a positive number, the sequence is ascending; if specified as a negative number, the sequence is descending. If not specified, the default value is1.CACHE: Specifies the number of auto-increment values pre-allocated in memory. The default is20.ORDER | NOORDER: Specifies whether the sequence values are generated in order.ORDERmeans generated in order,NOORDERmeans not generated in order. The default isNOORDER.CYCLE | NOCYCLE: Specifies whether the sequence values are generated cyclically.CYCLEmeans cyclic generation,NOCYCLEmeans non-cyclic generation. The default isNOCYCLE.
Notice
- If both
MINVALUEandMAXVALUEare specified,MINVALUEmust be less thanMAXVALUE. - The difference between
MAXVALUEandMINVALUEmust be greater than or equal toINCREMENT BY int_value. - The value of
CACHEmust be greater than1. If the value ofCACHE INT_VALUEis1, it is equivalent toNOCACHE. - If
CYCLEis specified and the value ofINCREMENT BYis less than0,MINVALUEmust be specified. - If
CYCLEis specified, the number of cached values must not exceed the number of values in one cycle.
The following example creates a sequence named seq1 with an auto-increment step of 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 sequences, see the following topics: