CREATE SEQUENCE statement

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 sequenceName [ sequenceElement ]*

The sequence name is composed of an optional schemaName and a SQLIdentifier. 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.

Performance

To boost performance and concurrency, Derby preallocates ranges of upcoming values for sequences. The lengths of these ranges can be configured by adjusting the value of the derby.language.sequence.preallocator property.

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;