Modify a sequence

After you create a sequence, you can use the ALTER SEQUENCE statement to modify the attributes of the sequence. OceanBase Database allows you to modify the minimum value, maximum value, increment step, and cycling flag of a sequence. You cannot modify the start value of a sequence.

SQL syntax for modifying a sequence:

ALTER SEQUENCE [database_name.]sequence_name
[MINVALUE int_value | NOMINVALUE]
[MAXVALUE int_value | NOMAXVALUE]
[INCREMENT BY int_value]
[CACHE int_value | NOCACHE]
[ORDER | NOORDER]
[CYCLE | NOCYCLE];

To execute this statement, you must have the ALTER privilege.

Example: Modify the maximum value of the seq2 sequence and enable cyclic generation of sequence values.

obclient> ALTER SEQUENCE seq2 MAXVALUE 1024 CYCLE;

OceanBase Database also allows you to reset sequence values by adding the RESTART option. Examples:

• If the value of INCREMENT BY is greater than 0, you can reset the sequence to make the sequence start from MINVALUE. If the value of INCREMENT BY is less than 0, you can execute the following statement to reset the sequence to make the sequence start from MAXVALUE:

  obclient> ALTER SEQUENCE seq3 RESTART;
  Query OK, 0 rows affected
  

• Execute the following statement to reset the sequence and make the sequence start from 3:

  obclient> ALTER SEQUENCE seq4 RESTART START WITH 3;
  Query OK, 0 rows affected
  

In the sequence value reset statement, RESTART is not necessarily placed after the sequence name. It can be placed anywhere in the statement, as shown in the following example:

obclient> ALTER SEQUENCE seq4 START WITH 3 RESTART MINVALUE -100 CYCLE;
Query OK, 0 rows affected

For more information about the ALTER SEQUENCE statement, see ALTER SEQUENCE.

More information

For more information about operations on a sequence, see the following topics:

• Create a sequence

• Query sequences

• Reference a sequence

• Drop a sequence