Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

CREATE SEQUENCE

Syntax

CREATE SEQUENCE [owner_name.]sequence_name
[ START WITH start_value ]
[ INCREMENT BY increment_value ]
[ MAXVALUE max_value | NOMAXVALUE ]
[ MINVALUE min_value | NOMINVALUE ]
[ CYCLE | NOCYCLE ]

Description

The CREATE SEQUENCE command creates a new sequence in the current database.

  • sequence_name is the name that is to be assigned to the sequence. The sequence_name in a database must be unique.
  • start_value specifies a starting value for the sequence. The value of start_value must be between -9223372036854775808 and 9223372036854775807. The default is 1.
  • increment_value specifies the value by which the sequence is incremented when sequence.NEXTVAL is called. Increment by can be positive or negative. The default is 1.
  • max_value specifies the maximum value that can be returned by the sequence. If the sequence is a cycling sequence, then the next value after the max_value will be min_value for an ascending sequence and the next value after min_value will be max_value for an ascending sequence. Note that the value of increment_value may mean that the exact max_value or min_value will never be returned. The default is 9223372036854775807.
  • min_value specifies the minimum value that can be returned by the sequence. The default min_value is -9223372036854775808.

When CYCLE is specified, a sequence will wrap from the max_value to the min_value for an ascending sequence or from the min_value to a max_value for a descending sequence. When NOCYCLE (the default) is specified, an error is returned when NEXTVAL (see Sequence Values) would exceed the max_value for an ascending sequence or the min_value for a descending sequence.

Example

The following command creates a sequence called myseq. It starts with a value of 5, increments by 5, and returns a maximum value of 50:

CREATE SEQUENCE myseq START WITH 5 INCREMENT BY 5 MAXVALUE 50;

Return Values

Error Code

Message

Returned By

-20265

Sequence with the same name already exists

CREATE SEQUENCE

-20268

START-WITH/CURRENT-VALUE cannot be greater than MAXVALUE

CREATE SEQUENCE
ALTER SEQUENCE

-20269

START-WITH/CURRENT-VALUE cannot be less than MINVALUE

CREATE SEQUENCE
ALTER SEQUENCE

-20270

Invalid sequence MINVALUE specified

CREATE SEQUENCE
ALTER SEQUENCE

-20271

Invalid sequence INCREMENT specified

CREATE SEQUENCE
ALTER SEQUENCE

-20272

START-WITH cannot be altered in sequence

CREATE SEQUENCE
ALTER SEQUENCE

-20273

No options specified for ALTER SEQUENCE

CREATE SEQUENCE
ALTER SEQUENCE

-20274

Sequence increment has exceeded MAXVALUE

CREATE SEQUENCE
ALTER SEQUENCE

-20275

Sequence decrement has exceeded MINVALUE

CREATE SEQUENCE
ALTER SEQUENCE

-20276

Only SELECT and ALTER privileges are valid for sequences

CREATE SEQUENCE
ALTER SEQUENCE

See c-tree Plus Error Codes for a complete listing of valid c-tree Plus error values.

See Also

TOCIndex