Creates a new sequence.
A new sequence is created.
sequence-nameshould follow the normal rules for naming database objects, see Naming Objects.
sequence-nameis specified in its unqualified form, the sequence will be created in the schema which has the same name as the current ident.
sequence-nameis 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).
sequence-namewill return the current value of the sequence, see CURRENT_VALUE.
sequence-namewill return the next value for the sequence and this will be established as the current value of the sequence, see NEXT_VALUE.
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 exceeding 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.
The sequence must be created in a databank on which the current ident has
IN databank-nameis not specified, the system will choose a databank on which the user has
Two sequences with the same name cannot belong to the same schema.
An ident must have
USAGEprivilege on the sequence in order to use it.
A databank used to store sequences must have either
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
sequence-nameis 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.
sequence-nameis used when the current value of the sequence is undefined, an error will be raised.
sequence-nameconstructs may be used in contexts where a value-expression would normally be used. They may also be used in the
DEFAULTclause of the
The following non-unique sequence:CREATE SEQUENCE mseq01 INITIAL_VALUE = 1 INCREMENT = 3 MAX_VALUE = 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º
The following unique sequence:CREATE UNIQUE SEQUENCE seq02 INITIAL_VALUE = 1 INCREMENT = 3 MAX_VALUE = 10;
will generate the following series of values: 1, 4, 7, 10, 3, 6, 9, 2, 5, 8.
The following sequence:CREATE UNIQUE SEQUENCE seq03 INITIAL_VALUE = 1 INCREMENT = -1 MAX_VALUE = -5;
will generate the following series of values: 1, 0, -1, -2, -3, -4, -5.
Create a table which uses a sequence to generate a default key:CREATE SEQUENCE idseq; CREATE TABLE orders (orderid INTEGER DEFAULT NEXT_VALUE OF idseq, ...
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, 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.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40