Mimer SQL Reference Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer


CREATE SEQUENCE


Creates a new sequence.

Usage

Embedded/Interactive/ODBC/JDBC.

Description

A new sequence is created.

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).

The construct "CURRENT_VALUE OF sequence-name" will return the current value of the sequence, see The CURRENT_VALUE Function.

The construct "NEXT_VALUE OF sequence-name" will return the next value for the sequence and this will be established as the current value of the sequence, see The NEXT_VALUE Function.

A sequence can be defined as unique or non-unique (the default is non-unique).

A sequence generates a series of values by starting at the initial value and proceeding in increment steps. When the addition of an increment would produce a value that is greater than the specified maximum, the sequence cycles back into the beginning of the value series by the appropriate number of steps to generate the next value.

A sequence can make more than one pass between the initial value and the maximum value to generate its series of values, depending on the initial value, increment step and the maximum value.

A non-unique sequence will continue to cycle indefinitely and thus may repeatedly generate its series of values.

A unique sequence will only go through its value-generating cycle once and is guaranteed never to return the same value twice.

Examples

A non-unique sequence with initial value 1, increment 3 and maximum 10 will generate the following (repeating) series of values: 1, 4, 7, 10, 3, 6, 9, 2, 5, 8, 1, 4, 7, 10, 3, 6, 9, 2, 5, 8, 1, 4, 7, 10, 3, 6 .

A unique sequence with initial value 1, increment 3 and maximum 10 will generate the following series of values: 1, 4, 7, 10, 3, 6, 9, 2, 5, 8.

Note: It is possible that not every value in the series of values defined by the sequence will be generated. If a database server crash etc. occurs during the life of a sequence, is possible that some of the values in the series might be skipped.

Restrictions

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.

Notes

If an initial value is not specified, a value of 1 is assumed.

If an increment is not specified, a value of 1 is assumed.

If a maximum value is not specified, a value equal to the largest INTEGER (with no precision specified) value possible in Mimer SQL is assumed (i.e. 2,147,483,647).

The sequence is created with an undefined current value initially. When "NEXT_VALUE OF 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 OF sequence-name" is used when the current value of the sequence is undefined, an error will be raised.

The "CURRENT_VALUE OF sequence-name" and "NEXT_VALUE OF sequence-name" constructs may be used in contexts where a value-expression would normally be used. They may also be used in the DEFAULT clause of the ALTER TABLE, CREATE DOMAIN and CREATE TABLE statements.

Standard Compliance

Standard
Compliance
Comments
X/Open-95
SQL92
MIMER EXTENSION
The CREATE SEQUENCE statement is a Mimer SQL extension.



Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL Reference Manual TOC PREV NEXT INDEX