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: -10^27^ to (10^27^-1). If you set this parameter to NOMINVALUE, the minimum value is 1 for the ascending order and the minimum value is -(10^27^-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: (-10^27^+1) to 10^27^. If you set this parameter to NOMAXVALUE, the maximum value is (10^28^-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 modifyINCREMENT BYto modify the start position.
For other considerations of the auto-increment column values, see the CREATE SEQUENCE statement.