ALTER SEQUENCE

Description

This statement is used to change the increment, minimum, and maximum values, cache size, and behavior of an existing sequence. The changes only affect subsequent sequence numbers.

info

Users can only operate on sequences in their own schema or those for which they have the ALTER object 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]
}
;

Parameter Description

Parameter	Description
schema.	Specifies the schema containing the sequence. If omitted, the database will modify the sequence in the current user's schema.
sequence_name	The name of the sequence to be modified.
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 [-(1027-1) ~ (1027-1)].
NOMINVALUE	If changed to NOMINVALUE, the minimum value is 1 when ascending and -(1027-1) when descending.
MAXVALUE int_value	Changes the maximum value of the sequence. The range of int_value is [(-1027+1) ~ (1028-1)]. The new MAXVALUE must be greater than or equal to the current sequence number. Note When both MINVALUE and MAXVALUE are specified, MINVALUE must be less than MAXVALUE. Additionally, the difference between MAXVALUE and MINVALUE must be greater than or equal to the int_value specified in INCREMENT BY.
NOMAXVALUE	If changed to NOMAXVALUE, the maximum value is (1028-1) 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 increment values pre-allocated in memory. The default value of int_value is 20. CACHE int_value must be greater than 1. If CACHE INT_VALUE is set to 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 will cache 20 sequence numbers by default.
ORDER	Changes the sequence value to be generated in order.
NOORDER	If changed 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 If CYCLE is specified and INCREMENT BY is a negative number, MINVALUE must be specified. If CYCLE is specified, the number of CACHE values cannot exceed one cycle.
NOCYCLE	The default value is NOCYCLE. If changed 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 seq1 and specify that the increment values are generated in a cycle.

  ALTER SEQUENCE seq1 MAXVALUE 1024 CYCLE;

  Query OK, 0 rows affected (0.001 sec)

• Change the maximum value and increment step of the sequence seq3, enable CYCLE and ORDER, and specify NOCACHE to indicate that the sequence values are not pre-allocated.

  ALTER SEQUENCE seq3 MAXVALUE 1500 INCREMENT BY 5 CYCLE NOCACHE ORDER;

  Query OK, 0 rows affected (0.001 sec)

• Reset the sequence value and start from 3.

  ALTER SEQUENCE seq3 RESTART;

  ALTER SEQUENCE seq3 RESTART START WITH 3;

• The position of RESTART can be anywhere among the options. The following two shell statements that reset the sequence value are equivalent.

  ALTER SEQUENCE seq3 START WITH 3 RESTART MINVALUE  -100 CYCLE;

  ALTER SEQUENCE seq3 RESTAR START WITH 3 T MINVALUE  -100 CYCLE;