How to Use Sequences as Column Defaults in Oracle

A sequence generates unique numbers independently of any table. Since a column's DEFAULT can be sequence.NEXTVAL, a key can be filled from a sequence without a trigger, and several tables can share one series of numbers, which identity columns cannot do. This guide covers creating sequences, their options, and using them as defaults.

Code for This Guide

The main examples are in the examples/schema-objects folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

create sequence [if not exists] sequence
  [start with n] [increment by n] [minvalue n] [maxvalue n]
  [cycle | nocycle] [cache n | nocache]

column number default sequence.nextval

NEXTVAL returns the next number, and CURRVAL the last one this session took. A number, once taken, is never given out again, even if the transaction rolls back, so sequences have gaps.

Create a Sequence

Example:

create sequence booking_ref_seq start with 1000 increment by 1 cache 20;

select booking_ref_seq.nextval as first, booking_ref_seq.nextval as same_row from dual;
select booking_ref_seq.nextval as second from dual;
select booking_ref_seq.currval as current_value from dual;

Output:

Sequence BOOKING_REF_SEQ created.

   FIRST    SAME_ROW
________ ___________
    1000        1000

   SECOND
_________
     1001

   CURRENT_VALUE
________________
            1001

Within one row of a query, every NEXTVAL returns the same number: both columns of the first query show 1000.

Sequence Options

CYCLE restarts at the minimum after the maximum, here three gates in turn. ALTER SEQUENCE changes the increment and RESTART sets the next value.

Example:

create sequence gate_seq minvalue 1 maxvalue 3 cycle nocache;

select level as call_no, gate_seq.nextval as gate from dual connect by level <= 5;

alter sequence booking_ref_seq increment by 10;
alter sequence booking_ref_seq restart start with 5000;
select booking_ref_seq.nextval as after_restart from dual;

select sequence_name, increment_by, cycle_flag, cache_size, last_number
from   user_sequences
where  sequence_name in ('BOOKING_REF_SEQ', 'GATE_SEQ');

Output:

Sequence GATE_SEQ created.

   CALL_NO    GATE
__________ _______
         1       1
         2       2
         3       3
         4       1
         5       2

Sequence BOOKING_REF_SEQ altered.

Sequence BOOKING_REF_SEQ altered.

   AFTER_RESTART
________________
            5000

SEQUENCE_NAME         INCREMENT_BY CYCLE_FLAG       CACHE_SIZE    LAST_NUMBER
__________________ _______________ _____________ _____________ ______________
BOOKING_REF_SEQ                 10 N                        20           5200
GATE_SEQ                         1 Y                         0              3

USER_SEQUENCES shows LAST_NUMBER running ahead of the numbers used, because CACHE 20 reserves numbers in memory.

A Sequence as a Default

The sequence, now incrementing by 10 from 5000, fills the key of a new table.

Example:

create table gate_changes (
  change_id  number default booking_ref_seq.nextval primary key,
  flight_id  number,
  new_gate   varchar2(4)
);
insert into gate_changes (flight_id, new_gate) values (2800, 'B12'), (2801, 'C4');
select * from gate_changes;

Output:

Table GATE_CHANGES created.

2 rows inserted.

   CHANGE_ID    FLIGHT_ID NEW_GATE
____________ ____________ ___________
        5010         2800 B12
        5020         2801 C4

The keys are 5010 and 5020: the restart consumed 5000, and the increment is 10. The example drops the table and the sequence afterward.

One Series for Two Tables

Example:

-- two tables drawing from one series: their keys never collide
insert into notes_a (txt) values ('first');
insert into notes_b (txt) values ('second');
insert into notes_a (txt) values ('third');
select 'A' as tab, note_id, txt from notes_a
union all
select 'B', note_id, txt from notes_b
order  by note_id;

Output:

1 row inserted.

1 row inserted.

1 row inserted.

TAB       NOTE_ID TXT
______ __________ _________
A               1 first
B               2 second
A               3 third

Both tables draw from note_seq, so their keys never collide, which helps when rows from both are later combined. The example creates and drops its own sequence and tables.

Things to Know

• For a key used by one table only, an identity column is simpler.
• Do not expect sequences to be gap-free: rollbacks, cache loss at restart, and other sessions all leave gaps.
• NOCACHE avoids losing cached numbers at restart, at the cost of speed.

Related Guides

• How to Create Identity Columns in Oracle
• Oracle SYS_GUID Function

Conclusion

Sequences generate unique numbers with gaps, and DEFAULT sequence.NEXTVAL fills a column from one without a trigger. Use them when several tables share a series of numbers, set options such as INCREMENT BY, CYCLE, and CACHE to fit, and use identity columns for single-table keys.