Creating a Sequence

A sequence generates a sequence of integers. You can use the integers generated by a sequence to populate a numeric primary key column. You create a sequence using the CREATE SEQUENCE statement:


CREATE SEQUENCE sequence_name
[START WITH start_num]
[INCREMENT BY increment_num]
[ { MAXVALUE maximum_num | NOMAXVALUE } ]
[ { MINVALUE minimum_num | NOMINVALUE } ]
[ { CYCLE | NOCYCLE } ]
[ { CACHE cache_num | NOCACHE } ]
[ { ORDER | NOORDER } ];

Option	Description
sequence_name	name of the sequence.
start_num	the integer to start the sequence. The default is 1.
increment_num	the integer to increment. The default is 1.
maximum_num	the maximum integer of the sequence;
NOMAXVALUE	specifies the maximum is 10²⁷ for an ascending sequence or -1 for a descending sequence. NOMAXVALUE is the default.
minimum_num	the minimum integer of the sequence;
NOMINVALUE	specifies the minimum is 1 for an ascending sequence or -1026 for a descending sequence. NOMINVALUE is the default.
CYCLE	the sequence generates integers even after reaching its maximum or minimum value.
NOCYCLE	the sequence cannot generate any more integers after reaching its maximum or minimum value. NOCYCLE is the default.
cache_num	the number of integers to keep in memory. The default is 20.
NOCACHE	no caching.
ORDER	guarantees the integers are generated in the order of the request.
NOORDER	doesn't guarantee the integers are generated in the order of the request. NOORDER is the default.

The following CREATE SEQUENCE statement omits the optional parameters, the default values are used. The start_num and increment_num of my_seq are set to the default of 1.


SQL> CREATE SEQUENCE my_seq;

Sequence created.

SQL>

The following example creates a sequence named my_seq2 and specifies values for the optional parameters:


SQL> CREATE SEQUENCE my_seq2
  2  START WITH 10 INCREMENT BY 5
  3  MINVALUE 10 MAXVALUE 20
  4  CYCLE CACHE 2 ORDER;

Sequence created.

SQL>

The following example creates a sequence named my_seq3 that starts at 10 and counts down to 1:


SQL> CREATE SEQUENCE my_seq3
  2  START WITH 10 INCREMENT BY -1
  3  MINVALUE 1 MAXVALUE 10
  4  CYCLE CACHE 5;

Sequence created.

SQL>

Home »
Oracle »
Table »

Sequence: