Create a sequence|V4.0.0|OceanBase Database| docs|Distributed Database

Create a sequence

Last Updated：2023-07-24 09:52:12 Updated

You can use the CREATE SEQUENCE statement to create a sequence. This topic describes how to create a sequence.

Prerequisites

You have the CREATE and ALTER privileges on sequences.

Description

You can use this statement to create a sequence. A sequence is a database object. You can generate a unique integer from a sequence.

If two users increment the same sequence concurrently, the sequence numbers obtained by the two users have an interval. One user can never obtain the sequence number generated by the other user. Sequence numbers are generated independent of the table. Therefore, you can use the same sequence for multiple tables.

After creating a sequence, you can use the CURRVAL pseudocolumn to return the current sequence value or use the NEXTVAL pseudocolumn to return the new increment value in the SQL statement.

Syntax

CREATE SEQUENCE [schema. ] 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];

Parameters

Parameter	Description
MINVALUE int_value \| NOMINVALUE	The minimum value of the sequence. Value range: [(-10²⁷+1),(10²⁸-1)]. If `NOMINVALUE` is specified, the minimum value is 1 for an ascending sequence, and -(10²⁷-1) for a descending sequence. If no minimum value is specified, `NOMINVALUE` takes effect by default.
MAXVALUE int_value	NOMAXVALUE	The maximum value of the sequence. Value range: [(-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 no maximum value is specified, `NOMAXVALUE` takes effect by default.
START WITH int_value	The start value of the sequence. The value must be smaller 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 int_value	The 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 is 1.
CACHE int_value	NOCACHE	The number of sequence values that the database preallocates and keeps in memory. The default value is 20.
ORDER	NOORDER	Specifies whether sequence values are generated in order. The default value is `NOORDER`.
CYCLE	NOCYCLE	Specifies whether sequence values are generated cyclically. The default value is `NOCYCLE`.

Examples

Create a sequence named seq1 and specify the increment to 1. In the following example, 1 is returned for the first reference to seq1.nextval, and 2 is returned for the second reference. The value returned for each subsequent reference increments by 1.

Create a sequence named seq1.
```
obclient> CREATE SEQUENCE seq1 MINVALUE 1 MAXVALUE 5 NOCYCLE NOORDER NOCACHE;
Query OK, 0 rows affected
```
Note

You can view the sequence that you have created in the USER_SEQUENCES, ALL_SEQUENCES, and DBA_SEQUENCES views.

Initiate the first reference to seq1.nextval.

obclient> SELECT seq1.nextval FROM DUAL;
+---------+
| NEXTVAL |
+---------+
|       1 |
+---------+
1 row in set

Initiate the second reference.

obclient> SELECT seq1.nextval FROM DUAL;
+---------+
| NEXTVAL |
+---------+
|       2 |
+---------+
1 row in set