ALTER SEQUENCE statement

Use the ALTER SEQUENCE statement to modify the definition of a sequence object. This statement is an extension to the ANSI/ISO standard for SQL.

Syntax

^{(explicit id altseq002)
altseq002} ^{(explicit id altseq003)
altseq003} ALTER SEQUENCE [ owner. ] sequence [ { | { ^[] { NOCYCLE | CYCLE } | ^[] { CACHE size | NOCACHE } | ^[] { NOORDER | ORDER } } | { ^[]INCREMENT BYstep | ^[]RESTARTWITHrestart | ^[] { MAXVALUEmax | NOMAXVALUE } | ^[] { MINVALUEmin | NOMINVALUE } } } ]


Element	Description	Restrictions	Syntax
`max`	New upper limit on values	Must be integer > CURRVAL and `restart`	Literal Number
`min`	New lower limit on values	Must be integer < CURRVAL and `restart`	Literal Number
`owner`	Owner of `sequence`	Cannot be changed by this statement	Owner name
`restart`	New first value in sequence	Must be integer in the INT8 range	Literal Number
`sequence`	Name of existing sequence	Must exist. Cannot be a synonym.	Identifier
`size`	New number of values to preallocate in memory	Integer > 2 but < cardinality of values in one cycle (= \|(`max` - `min`)/`step`\|)	Literal Number
`step`	New interval between successive values	Must be a nonzero integer	Literal Number

Usage

The ALTER SEQUENCE statement can update the definition of a specified sequence object in the syssequences system catalog table.

ALTER SEQUENCE redefines an existing sequence object. It only affects subsequently generated values (and any unused values in the sequence cache). You cannot use the ALTER SEQUENCE statement to rename a sequence nor to change the owner of a sequence.

You must be the owner, or the DBA, or else have the Alter privilege on the sequence to modify its definition. Only elements of the sequence definition that you specify explicitly in the ALTER SEQUENCE statement are modified. An error occurs if you make contradictory changes, such as specifying both MAXVALUE and NOMAXVALUE, or both the CYCLE and NOCYCLE options.

Examples

The examples below are based on the following sequence object and table:

CREATE SEQUENCE seq_2 
   INCREMENT BY 1 START WITH 1 
   MAXVALUE 30 MINVALUE 0 
   NOCYCLE CACHE 10 ORDER; 

CREATE TABLE tab1 (col1 int, col2 int); 
INSERT INTO tab1 VALUES (0, 0); 

INSERT INTO tab1 (col1, col2) VALUES (seq_2.NEXTVAL, seq_2.NEXTVAL)


SELECT * FROM tab1;

       col1        col2

          0           0
          1           1


ALTER SEQUENCE seq_2
   RESTART WITH 5
   INCREMENT by 2
   MAXVALUE 300;

INSERT INTO tab1 (col1, col2) VALUES (seq_2.NEXTVAL, seq_2.NEXTVAL)
INSERT INTO tab1 (col1, col2) VALUES (seq_2.NEXTVAL, seq_2.NEXTVAL)
SELECT * FROM tab1;

        col1        col2

          0           0
          1           1
          5           5
          7           7

¹ Use path no more than once

² Default specification