The CREATE SEQUENCE statement creates a sequence generator, which is
a mechanism for generating exact numeric values, one at a time.
The owner of the schema where the sequence generator lives automatically
gains the USAGE privilege on the sequence generator, and can grant this
privilege to other users and roles. Only the
database owner and the
owner of the sequence generator can grant these USAGE privileges. The USAGE
privilege cannot be revoked from the schema owner. See
GRANT statement and
REVOKE statement for more information.
Syntax
CREATE SEQUENCE [ schemaName. ] SQL92Identifier [ sequenceElement ]*
The sequence name is composed of an optional schemaName and a
SQL92Identifier. If a schemaName is not provided, the current
schema is the default schema. If a qualified sequence name is specified, the
schema name cannot begin with SYS.
sequenceElement
{
AS dataType
| START WITH signedInteger
| INCREMENT BY signedInteger
| MAXVALUE signedInteger | NO MAXVALUE
| MINVALUE signedInteger | NO MINVALUE
| CYCLE | NO CYCLE
}
If specified, the dataType must be an integer type (SMALLINT, INT, or
BIGINT). If not specified, the default data type is INT.
If specified, the INCREMENT value is a non-zero number which fits in a
DataType value. If not specified, the INCREMENT defaults to 1. INCREMENT
is the step by which the sequence generator advances. If INCREMENT is positive,
the sequence numbers get larger over time. If INCREMENT is negative, the
sequence numbers get smaller.
If specified, MINVALUE must be an integer which fits in a DataType
value. If MINVALUE is not specified, or if NO MINVALUE is specified, MINVALUE
defaults to the smallest negative number which fits in a DataType value.
If specified, MAXVALUE may not be greater than the largest positive integer
that fits in a DataType value. If MAXVALUE is not specified, or if
NO MAXVALUE is specified, MAXVALUE defaults to the largest positive integer
which fits in a DataType value. MAXVALUE must be greater than MINVALUE.
The START WITH clause specifies the initial value of the sequence generator.
This value must fall between MINVALUE and MAXVALUE. If the START WITH clause is
not specified, the initial value defaults to be:
- MINVALUE if INCREMENT is positive
- MAXVALUE if INCREMENT is negative
The CYCLE clause controls what happens when the sequence generator exhausts
its range and wraps around. If CYCLE is specified, the wraparound behavior is to
reinitialize the sequence generator to its START value. If NO CYCLE is
specified, Derby throws an
exception when the generator wraps around. The default behavior is NO CYCLE.
To retrieve the next value from a sequence generator, use a
NEXT VALUE FOR
expression.
Examples
The following statement creates a sequence generator of type INT, with a
start value of -2147483648 (the smallest INT value). The value increases by 1,
and the last legal value is the largest possible INT. If NEXT VALUE FOR is
invoked on the generator again,
Derby throws an exception.
CREATE SEQUENCE order_id;
The following statement creates a sequence of type BIGINT with a start value
of 3,000,000,000. The value increases by 1, and the last legal value is the
largest possible BIGINT. If NEXT VALUE FOR is invoked on the generator again,
Derby throws an exception.
CREATE SEQUENCE order_entry_id
AS BIGINT
START WITH 3000000000;
