Purpose
You can use this statement to modify the increment, minimum value, maximum value, cache size, and action of the current sequence. The modification affects only subsequent sequence values.
Note
You can modify only sequences in your schema or those for which you have the ALTER privilege.
Syntax
ALTER SEQUENCE [ schema. ] sequence_name
{ RESTART [ START WITH int_value ]
|[ MINVALUE int_value | NOMINVALUE ]
|[ MAXVALUE int_value | NOMAXVALUE ]
|[ INCREMENT BY int_value ]
|[ CACHE int_value | NOCACHE ]
|[ ORDER | NOORDER ]
|[ CYCLE | NOCYCLE]
}
;
Parameters
Parameter |
Description |
|---|---|
| schema. | The schema where the sequence resides. If this parameter is not specified, the database modifies the sequence in the schema of the current user. |
| sequence_name | The name of the sequence to be modified. |
| RESTART | Restarts the generation of sequence values. When the sequence ascends, new values start from MINVALUE. When the sequence descends, new values start from MAXVALUE. Alternatively, START WITH int_value is used to generate values starting from int_value. |
| MINVALUE int_value | The minimum value of the sequence. Value range: [-(1027-1), (1027-1)]. |
| NOMINVALUE | If NOMINVALUE is specified, the minimum value is 1 for an ascending sequence and -(1027-1) for a descending sequence. |
| MAXVALUE int_value | The maximum value of the sequence. Value range: [(-1027+1), (1028-1)]. The specified MAXVALUE value cannot be smaller than the current sequence value. Notice: When MINVALUE and MAXVALUE are both specified, MINVALUE must be smaller than MAXVALUE. The difference between MAXVALUE and MINVALUE must be greater than or equal to INCREMENT BY int_value. |
| NOMAXVALUE | If NOMAXVALUE is specified, the maximum value is (1028-1) for an ascending sequence and -1 for a descending sequence. |
| INCREMENT BY int_value | Modifies the increment of the sequence. int_value cannot be 0. If the value is positive, the sequence ascends. If the value is negative, the sequence descends. If this parameter is not specified, the default value is 1. |
| CACHE int_value | Modifies the number of sequence values pre-allocated in the memory. By default, int_value is 20. The value of CACHE int_value must be greater than 1. If the value of CACHE int_value is 1, it is equivalent to NOCACHE. |
| NOCACHE | Specifies that no sequence values are pre-allocated. If neither CACHE nor NOCACHE is specified, the database caches 20 sequence values by default. |
| ORDER | Specifies that sequence values are generated in order. |
| NOORDER | If NOORDER is specified, it is not assured that sequence values are generated in order. |
| CYCLE | Specifies that sequence values are generated cyclically. That is, when the sequence reaches its maximum or minimum value, it continues to generate values. If the sequence ascends and reaches its maximum value, the minimum sequence value will be generated. If the sequence descends and reaches its minimum value, the maximum sequence value will be generated. Notice
|
| NOCYCLE | NOCYCLE is the default value. If NOCYCLE is specified, no more values are generated when the sequence reaches its maximum or minimum value. |
Examples
Modify the maximum value of the
seq1sequence and enable cyclic generation of sequence values.obclient> ALTER SEQUENCE seq1 MAXVALUE 1024 CYCLE; Query OK, 0 rows affectedModify the maximum value and increment of the
seq3sequence, enableCYCLEandORDER, and specifyNOCACHEto indicate that the sequence values are not pre-allocated.obclient> ALTER SEQUENCE seq3 MAXVALUE 1500 INCREMENT BY 5 CYCLE NOCACHE ORDER; Query OK, 0 rows affectedRestart the sequence from 3.
obclient> ALTER SEQUENCE seq3 RESTART; Query OK, 0 rows affected obclient> ALTER SEQUENCE seq3 RESTART START WITH 3; Query OK, 0 rows affectedThe sequence can restart from any position. In the following example, the two SQL statements for restarting a sequence are equivalent.
obclient> ALTER SEQUENCE seq3 START WITH 3 RESTART MINVALUE -100 CYCLE; Query OK, 0 rows affected obclient> ALTER SEQUENCE seq3 RESTAR START WITH 3 T MINVALUE -100 CYCLE; Query OK, 0 rows affected