All Products
Search
Document Center

ALTER SEQUENCE

Last Updated: Jun 18, 2021

Description

This statement modifies an auto-increment column.

Syntax

ALTER SEQUENCE sequence_name
  [MINVALUE value | NOMINVALUE]
  [MAXVALUE value | NOMAXVALUE]
  [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

  • Modify the maximum value of the auto-increment column my_sequence and specify that the values of the auto-increment column are cyclically generated.

OceanBase(root@oceanbase)>ALTER SEQUENCE my_sequence MAXVALUE 1024 CYCLE;

Considerations

  • You cannot change the value of START WITH in ALTER SEQUENCE. If you need to modify the start position for the next sequence, you can modify INCREMENT BY

    to modify the start position.

  • For other considerations of the auto-increment column values, see the CREATE SEQUENCE statement.