Purpose
This statement changes the increment, minimum value, maximum value, cache size, and behavior of a sequence. The changes only affect sequences generated after the modification.
Note
You can modify a sequence only in the current schema or if you 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 description
| Field | Description |
|---|---|
| schema. | The schema in which the sequence is to be modified. If the schema is not specified, the database changes the sequence in the current schema of the user. |
| sequence_name | The name of the sequence to be modified. |
| RESTART | Resets the sequence value. If the sequence is generated in ascending order, the value starts from MINVALUE. If the sequence is generated in descending order, the value starts from MAXVALUE. You can also use START WITH int_value to specify the starting value, which is int_value. |
| MINVALUE int_value | The new minimum value of the sequence. int_value must be in the range of [-9223372036854775808,9223372036854775807]. |
| NOMINVALUE | If this option is specified, the minimum value of the sequence is 1 if the sequence is generated in ascending order, and -9223372036854775808 if the sequence is generated in descending order. |
| MAXVALUE int_value | The new maximum value of the sequence. int_value must be in the range of [-9223372036854775808,9223372036854775807]. The new MAXVALUE cannot be smaller than the current sequence number. Note
|
| NOMAXVALUE | If this option is specified, the maximum value of the sequence is 9223372036854775807 if the sequence is generated in ascending order, and -1 if the sequence is generated in descending order. |
| INCREMENT BY int_value | The new increment of the sequence. int_value cannot be 0. If int_value is positive, the sequence is generated in ascending order; if int_value is negative, the sequence is generated in descending order. If this clause is not specified, the default value is 1. |
| CACHE int_value | The number of cached auto-increment values. The default value is 20. int_value must be greater than 1. If CACHE int_value is specified as 1, it is equivalent to specifying NOCACHE. |
| NOCACHE | Specifies not to preallocate auto-increment values. If both CACHE and NOCACHE are not specified, the database caches 20 auto-increment values by default. |
| ORDER | Specifies to generate auto-increment values in order. |
| NOORDER | If this option is specified, the database does not guarantee that the auto-increment values are generated in order. |
| CYCLE | Specifies to generate auto-increment values in cycles. That is, the sequence generates values after reaching the maximum value or minimum value. If the sequence is generated in ascending order and reaches the maximum value, it generates the minimum value. If the sequence is generated in descending order and reaches the minimum value, it generates the maximum value. Note
|
| NOCYCLE | NOCYCLE is the default value. If this option is specified, the sequence cannot generate more values after reaching the maximum value or minimum value. |
Examples
Modify the maximum value of the auto-increment column
seq1and specify that the auto-increment values are cyclically generated.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 auto-increment step of the
seq2sequence, enableCYCLEandORDER, and specifyNOCACHEto indicate that the sequence values are not preallocated.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 position of
RESTARTcan be anywhere in the options. The following two SQL statements for resetting 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;