Purpose
This statement is used to modify the increment, minimum value, and maximum value, cache count, and behavior of an existing sequence. The changes only affect subsequent sequence numbers.
Note
You can only modify sequences in the current schema or have the ALTER privilege on the sequence.
Syntax
ALTER SEQUENCE [schema.]sequence_name
{
RESTART [START WITH integer]
| MINVALUE integer | NOMINVALUE
| MAXVALUE integer | NOMAXVALUE
| INCREMENT BY integer
| CACHE integer | NOCACHE
| ORDER | NOORDER
| CYCLE | NOCYCLE
}
Syntax
| Field | Description |
|---|---|
| schema. | Specifies the schema that contains the sequence. If you omit the schema, the database modifies the sequence in the current schema. |
| sequence_name | The name of the sequence to modify. |
| RESTART | Resets the sequence value. When ascending, it starts from MINVALUE; when descending, it starts from MAXVALUE; or, you can use START WITH int_value to start from int_value. |
| MINVALUE int_value | Changes the minimum value of the sequence. The range of int_value is [-9223372036854775808,9223372036854775807]. |
| NOMINVALUE | If you change it to NOMINVALUE, the minimum value is 1 when ascending and -9223372036854775808 when descending. |
| MAXVALUE int_value | Changes the maximum value of the sequence. The range of int_value is [-9223372036854775808,9223372036854775807]. The new MAXVALUE must be greater than the current sequence number. Note
|
| NOMAXVALUE | If you change it to NOMAXVALUE, the maximum value is 9223372036854775807 when ascending and -1 when descending. |
| INCREMENT BY int_value | Changes the increment step of the sequence. int_value cannot be 0. If specified as a positive number, the sequence is ascending; if specified as a negative number, the sequence is descending. The default value is 1 if not specified. |
| CACHE int_value | Changes the number of pre-allocated increment values 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. |
| NOCACHE | Changes the sequence value to not be pre-allocated. If both CACHE and NOCACHE are omitted, the database defaults to caching 20 |
| ORDER | Changes the sequence value to be generated in order. |
| NOORDER | If you change it to NOORDER, the sequence value is not guaranteed to be generated in order. |
| CYCLE | Changes the sequence value to be generated in a cycle. That is, the sequence continues to generate values after reaching its maximum or minimum value. When ascending, it generates the minimum value after reaching the maximum value. When descending, it generates the maximum value after reaching the minimum value. Note
|
| NOCYCLE | NOCYCLE is the default value. If you change it to NOCYCLE, the sequence cannot generate more values after reaching its maximum or minimum value. |
Examples
Modify the maximum value of the auto-increment column
seq1and specify that the increment values are generated in a cycle.obclient> CREATE SEQUENCE seq1 START WITH 1 INCREMENT BY 1 MAXVALUE 2048 CYCLE; obclient> ALTER SEQUENCE seq1 MAXVALUE 4096 CYCLE;Change the maximum value and increment step of the sequence
seq2, enableCYCLEandORDER, and specifyNOCACHEto indicate that the sequence values are not pre-allocated.obclient> CREATE SEQUENCE seq2 MINVALUE 1 MAXVALUE 1000 START WITH 1 INCREMENT BY 1 CACHE 20; obclient> ALTER SEQUENCE seq2 MAXVALUE 1500 INCREMENT BY 5 CYCLE NOCACHE ORDER;Reset the sequence value and start from 3.
obclient> ALTER SEQUENCE seq2 RESTART; obclient> ALTER SEQUENCE seq2 RESTART START WITH 3;The
RESTARToption can be placed anywhere in the clause. The following two SQL statements that reset the sequence value are equivalent.obclient> ALTER SEQUENCE seq2 START WITH 3 RESTART MINVALUE -100 CYCLE; obclient> ALTER SEQUENCE seq2 RESTART START WITH 3 MINVALUE -100 CYCLE;