Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


CREATE SEQUENCE


Creates a new sequence.



Usage

Embedded/Interactive/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.

data type
Lowest possible value
Highest possible value
SMALLINT
-32768
32767
INTEGER
-2147483648
2147483647
BIGINT
-9223372036854775808
9223372036854775807

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.

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. If IN databank-name is not specified, the system will choose a databank on which the user 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

Standard
Compliance
Comments
SQL-2011
Features outside core
Feature T176 "Sequence generator support".

Mimer SQL extension
The IN databank clause is a Mimer SQL extension.


Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX