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 change the start value, minimum value, maximum value, increment step, and cycle of a sequence.

The SQL syntax for modifying a sequence is as follows:

ALTER SEQUENCE [database_name.]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]
};

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

Here is an example:

1. Change the maximum value of the seq2 sequence and enable cyclic generation of sequence values.

   obclient> ALTER SEQUENCE seq2 MAXVALUE 1024 CYCLE;
   

2. Change the start value of the sequence.

   • 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
     

   When you change the start value of a sequence, RESTART does not necessarily need to be behind the sequence name. It can be at any position in the statement. A sample statement is as follows:

   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.

References

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

• Create a sequence

• Query sequences

• Reference a sequence

• Drop a sequence