All Products
Search
Document Center

CREATE SEQUENCE

Last Updated: Jun 18, 2021

Description

This statement creates an auto-increment column.

Syntax

CREATE SEQUENCE sequence_name
  [MINVALUE value | NOMINVALUE]
  [MAXVALUE value | NOMAXVALUE]
  [START WITH value]
  [INCREMENT BY value]
  [CACHE value | NOCACHE]
  [ORDER | NOORDER]
  [CYCLE | NOCYCLE];

Parameter description

Parameter

Description

MINVALUE value | NOMINVALUE

Specifies the minimum value of the auto-increment column. Valid values: -1027 to (1027-1).

If you set this parameter to NOMINVALUE, the minimum value is 1 for the ascending order and the minimum value is -(1027-1) for the descending order.

If no parameter is specified, the default value is NOMINVALUE.

MAXVALUE value | NOMAXVALUE

Specifies the maximum value of the auto-increment column. Valid values: (-1027+1) to 1027.

If you set this parameter to NOMAXVALUE, the maximum value is (1028-1) for the ascending order and the maximum value is -1 for the descending order.

If no parameter is specified, the default value is NOMAXVALUE.

START WITH value

Specifies the start value of the auto-increment column. This value must be less than or equal to MAXVALUE and greater than or equal to MINVALUE.

If no parameter is specified, the default value is the minimum value for the ascending order and the default value is the maximum value for the descending order.

INCREMENT BY value

Specifies the auto-increment step for the auto-increment column. The value cannot be 0.

If you specify this parameter as a positive number, the auto-increment column is in ascending order. If you specify this parameter as a negative number, the auto-increment column is in descending order.

If no parameter is specified, the default value is 1.

CACHE value | NOCACHE

Specifies the number of preassigned auto-increment values in the memory. Default value: 20.

ORDER | NOORDER

Specifies whether the values of the auto-increment column are generated in sequence. Default value: NOORDER.

CYCLE | NOCYCLE

Specifies whether the values of the auto-increment column are cyclically generated. Default value: NOCYCLE.

Examples

  • Create an auto-increment column s1.

OceanBase(ADMIN@TEST)>create sequence S1 minvalue 1 maxvalue 5 nocycle noorder cache 10240000;

Considerations

  • If you specify both MINVALUE and MAXVALUE, MINVALUE must be smaller than MAXVALUE.

  • The difference between MAXVALUE and MINVALUE must be greater than or equal to INCREMENT BY value.

  • The value of the CACHE value must be greater than 1. If the value of CACHE value is 1, the value is equivalent to NOCACHE.

  • If you specify CYCLE and INCREMENT BY value is smaller than 0, you must specify MINVALUE.

  • If you specify CYCLE, the number of caches cannot exceed one cycle.