Skip to main content

CREATE SEQUENCE

Description​

This statement is used to create a sequence. A sequence is a database object that generates unique integers.

If two users increment the same sequence at the same time, the sequence numbers obtained by each user may have gaps, and one user will never obtain the sequence number generated by the other user. Sequence numbers are generated independently of tables, so the same sequence can be used for one or more tables.

After a sequence is created, you can use the CURRVAL pseudo-column in an SQL statement to return the current value of the sequence or use the NEXTVAL pseudo-column to return the next incremented value. For more information, see Sequence pseudo-columns.

Syntax​

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

Parameter description​

ParameterDescription
schema.Specifies the schema that contains the sequence. If you omit the schema, the database creates the sequence in the current schema.
sequence_nameThe name of the sequence to be created.
IF NOT EXISTSIf the specified sequence name already exists and you do not specify IF NOT EXISTS, an error is returned.
MINVALUE int_valueSpecifies the minimum value of the sequence. The range of int_value is [-(1027-1) ~ (1027-1)].
NOMINVALUENOMINVALUE is the default. If you specify NOMINVALUE, the minimum value is 1 when the sequence is ascending and -(1027-1) when the sequence is descending.
MAXVALUE int_valueSpecifies the maximum value of the sequence. The range of int_value is [(-1027+1) ~ (1028-1)].
Notice
  • When you specify both MINVALUE and MAXVALUE, MINVALUE must be less than MAXVALUE.
  • The difference between MAXVALUE and MINVALUE must be greater than or equal to the int_value specified in INCREMENT BY.
NOMAXVALUENOMAXVALUE is the default. If you specify NOMAXVALUE, the maximum value is (1028-1) when the sequence is ascending and -1 when the sequence is descending.
START WITH int_valueSpecifies the starting value of the sequence. int_value must be less than or equal to MAXVALUE and greater than or equal to MINVALUE. If you do not specify this parameter, the default value is the minimum value when the sequence is ascending and the maximum value when the sequence is descending.
INCREMENT BY int_valueSpecifies the increment step of the sequence. int_value cannot be 0. If you specify a positive number, the sequence is ascending; if you specify a negative number, the sequence is descending. The default value is 1.
CACHE int_valueSpecifies the number of incremented values to be preallocated in memory. The default value is 20. CACHE int_value must be greater than 1. If CACHE INT_VALUE is 1, it is equivalent to NOCACHE.
NOCACHESpecifies that the sequence values are not preallocated. If you omit both CACHE and NOCACHE, the database preallocates 20 sequence numbers by default.
ORDERSpecifies that the sequence values are generated in order.
NOORDERNOORDER is the default, indicating that the sequence values are not guaranteed to be generated in order.
CYCLESpecifies that the sequence values are generated in a cycle. That is, the sequence continues to generate values after reaching its maximum or minimum value. When the sequence reaches its maximum value, it generates the minimum value. When the sequence reaches its minimum value, it generates the maximum value.
Notice
  • If you specify CYCLE, you must also specify MINVALUE when INCREMENT BY int_value is less than 0.
  • If you specify CYCLE, the number of CACHE values cannot exceed one cycle.
NOCYCLENOCYCLE is the default, indicating that the sequence cannot generate more values after reaching its maximum or minimum value.

Examples​

Create a sequence seq1 in the test schema and specify an increment step of 2. In the following example, the first reference to seq1.nextval returns 1, the second reference returns 3, and each subsequent reference returns a value that is 2 greater than the previous one.

CREATE SEQUENCE test.seq1 START WITH 1 MINVALUE 1 MAXVALUE 10 INCREMENT BY 2 NOCYCLE NOORDER CACHE 30;
Query OK, 0 rows affected (0.047 sec)

SELECT seq1.nextval FROM DUAL;
+--------+
| nextval|
+--------+
| 1      |
+--------+
1 row in set (0.001 sec)

SELECT seq1.nextval FROM DUAL;
+--------+
| nextval|
+--------+
| 3      |
+--------+
1 row in set (0.001 sec)

Contents

Edit this page