CREATE SEQUENCE
Defines a new named sequence number generator object.
Use sequences or auto-incrementing columns for primary key columns. For example, to generate only even numbers in a sequence, specify a start value of 2, and increment the sequence by 2. Sequences guarantee uniqueness and avoid constraint enforcement problems and their associated overhead.
Syntax
CREATE SEQUENCE [schema.]sequence-name ... [ INCREMENT [ BY ] positive-or-negative ] ... [ MINVALUE minvalue | NO MINVALUE ] ... [ MAXVALUE maxvalue | NO MAXVALUE ] ... [ START [ WITH ] start ] ... [ CACHE value | NO CACHE ] ... [ CYCLE | NO CYCLE ]
Parameters
schema |
Specifies a schema. If multiple schemas are defined in the database, include the schema name. For example: myschema.thisDbObject |
sequence-name |
The name of the sequence to create, where sequence-name conforms to conventions described in Identifiers. It must also be unique among sequences, tables, projections, and views. |
INCREMENT [BY] positive-or-negative |
Specifies how much to increment (or decrement) the current sequence value. A positive value creates an ascending sequence; a negative value makes a descending sequence. The default value is 1. |
MINVALUE minvalue | NO MINVALUE |
Determines the minimum value a sequence can generate. If you do not specify this clause, or you specify NO MINVALUE, default values are used. The defaults are 1 and -263-1 for ascending and descending sequences, respectively. |
MAXVALUE maxvalue | NO MAXVALUE |
Determines the maximum value for the sequence. If this clause is not supplied or you specify NO MAXVALUE, default values are used. The defaults are 263-1 and -1 for ascending and descending sequences, respectively. |
START [WITH] startvalue |
Specifies a specific start value of the sequence (startvalue). The default values are minvalue for ascending sequences and maxvalue for descending sequences. |
CACHE value | NO CACHE |
Specifies how many sequence numbers are pre-allocated and stored in memory for faster access. The default is 250,000 with a minimum value of 1. The default cache size provides good efficiency for large insert or copy operations Specifying a cache value of 1 indicates that only one value can be generated at a time, since no cache is assigned. Alternatively, you can specify NO CACHE. Notes:
|
CYCLE | NO CYCLE |
Specifies whether the sequence can wrap when its minimum or maximum values are reached:
|
Privileges
To create a sequence, the user must have CREATE privilege on the schema to contain the sequence. Only the owner and superusers can initially access the sequence. All other users must be granted access to the sequence by a superuser or the owner.
To create a table with a sequence, the user must have SELECT privilege on the sequence and USAGE privilege on the schema that contains the sequence.
Note: Referencing a named sequence in a CREATE TABLE statement requires SELECT privilege on the sequence object and USAGE privilege on the schema of the named sequence.
Incrementing and Obtaining Sequence Values
After creating a sequence, use the NEXTVAL function to create a cache in which the sequence value is stored. Use the CURRVAL function to get the current sequence value.
You cannot use NEXTVAL or CURRVAL to act on a sequence in a SELECT statement:
- in a WHERE clause
- in a GROUP BY or ORDER BY clause
- in a DISTINCT clause
- along with a UNION
- in a subquery
Additionally, you cannot use NEXTVAL or CURRVAL to act on a sequence in:
- a subquery of UPDATE or DELETE
- a view
You can use subqueries to work around some of these restrictions. For example, to use sequences with a DISTINCT clause:
=> SELECT t.col1, shift_allocation_seq.nextval FROM ( SELECT DISTINCT col1 FROM av_temp1) t;
Removing a Sequence
Use the DROP SEQUENCE function to remove a sequence. You cannot drop a sequence upon which other objects depend. Sequences used in a default expression of a column cannot be dropped until all references to the sequence are removed from the default expression.
DROP SEQUENCE ... CASCADE is not supported.
Examples
See Using Named Sequences in the Administrator's Guide.