CREATE SEQUENCE

Creates a new sequence.

 

Usage

Embedded, Interactive, Module, ODBC, JDBC.

Description

A sequence generates a series of exact numeric values by starting at the start value and proceeding in steps as defined by the increment value. The increment can either be positive or negative. If increment is positive the sequence is called an ascending sequence, and if increment is negative it is a descending sequence. The default increment value is 1.

If no start value is specified, the start value for a regular ascending sequence is MINVALUE, and for a descending sequence it is MAXVALUE.

The default MINVALUE is 1. The default MAXVALUE is the highest possible value (depends on the data type, see table below).

MINVALUE, MAXVALUE, start value and increment value must all be between the limits for the data type for the sequence.

If no data type is specified, INTEGER is default.

Start value must be between MINVALUE and MAXVALUE (if specified).

The set of possible values for a sequence is limited by MINVALUE and MAXVALUE. If CYCLE option is specified for the sequence these values will be generated endlessly, while if NO CYCLE is specified, the sequence will be exhausted once all possible values has been generated. NO CYCLE is the default if cycle option is not specified.

To generate a new value for a sequence the expression

next value for sequence-name

 

is used. This can be used in all DML-statements where an expression is allowed. It can also be used in the default clause for a column or for a domain definition. See NEXT VALUE.

To get the latest generated value within a session the expression

current value for sequence-name

 

is used. The generated value is kept for each session. This means that current value is not affected by other users using the same sequence. See CURRENT VALUE.

The IN databank-name specifies in which databank sequence data should be stored. The user creating the sequence must have SEQUENCE privilege on the databank.

If IN databank-name is not specified, the system will choose a databank on which the user has SEQUENCE privilege. If more than one such databank exists, databanks created by the current ident are chosen in preference to others and the databank with the most secure transaction option is chosen (i.e. a databank with LOG option would be chosen in preference to one with TRANSACTION option).

Restrictions

The sequence-name should follow the normal rules for naming database objects, see Naming Objects.

If sequence-name is specified in its unqualified form, the sequence will be created in the schema which has the same name as the current ident.

If sequence-name is specified in its fully qualified form (i.e. schema-name.sequence-name) the sequence will be created in the named schema (in this case, the current ident must be the creator of the specified schema).

Two sequences with the same name cannot belong to the same schema.

An ident must have USAGE privilege on the sequence in order to use it.

The sequence must be created in a databank on which the current ident has SEQUENCE privilege.

A databank used to store sequences must have TRANSACTION or LOG option.

Notes

The sequence is created with an undefined current value initially. When NEXT VALUE FOR sequence-name is used for the first time after the sequence is created, the initial value for the sequence is returned and established as the current value of the sequence.

If CURRENT VALUE FOR sequence-name is used when the current value of the sequence is undefined, an error will be raised.

Examples

A sequence with default options:

create sequence mseq01;

 

When used this sequence will generate values between 1 and 2147483647 in steps of one, starting with the value 1.

A smallint based sequence:

create sequence mseq02

     as smallint

     start with 2

     increment by 3

     minvalue 1

     maxvalue 10

     cycle;

 

This sequence will generate the following (repeating) series of values:

   2, 5, 8, 1, 4, 7, 10, 1, 4, 7, 10 ...

A bigint based sequence:

create sequence mseq03 as bigint

     increment by -1;

 

When used this sequence will generate values between 9223372036854775807 and 1 in descending steps of one.

Use a sequence for column default:

create sequence idseq start with 1;

create table orders (orderid integer default next value for idseq,

                     primary key (orderid),

                     purchasedate date,

                     customerid integer references customer);

 

If a new row is inserted into the orders table without specifying a value for the orderid column, the sequence will be used to generate a new unique value for the column.

Note:It is possible that not every value in the series of values defined by the sequence will be generated. In case of a server failure it is possible that some of the values in the series might be skipped.

For more information, see the Mimer SQL User's Manual, Creating Sequences.

Standard Compliance