Skip to content
Tutorials Center
My learning

Topics

Lesson 8 of 38 ยท SQL in Oracle

Sequences and Identity Columns

Generate unique keys in Oracle with CREATE SEQUENCE, NEXTVAL and CURRVAL, identity columns, DEFAULT sequence values, caching, gaps and restarting a sequence.

Most tables need a key that is unique for every row, and the key is best produced by the database rather than guessed by the application. Oracle offers two tools. A sequence is a separate object that hands out numbers on request. An identity column (since 12c) wraps a hidden sequence into the column definition, so you do not have to think about it.

Neither tool is available in the code runner, so the Oracle syntax is read-only. Runnable stand-ins at the end use SQLite's auto-numbering, which is the closest equivalent.

Creating and using a sequence

SQL
CREATE SEQUENCE order_seq
  START WITH 1000
  INCREMENT BY 1
  NOCACHE
  NOCYCLE;

You read a sequence with two pseudocolumns:

Expression Returns
order_seq.NEXTVAL The next number (increments the sequence)
order_seq.CURRVAL The last number this session got from NEXTVAL
SQL
INSERT INTO orders (order_id, customer_id, ordered_on)
VALUES (order_seq.NEXTVAL, 42, SYSDATE);

INSERT INTO order_lines (order_id, product_id, quantity)
VALUES (order_seq.CURRVAL, 7, 2);      -- same id as the order above

CURRVAL only works after you have called NEXTVAL in the current session; otherwise Oracle raises ORA-08002: sequence ORDER_SEQ.CURRVAL is not yet defined in this session. Sessions never see each other's current values, so two users inserting at once get different numbers without waiting for each other.

From 12.1 on, a NEXTVAL can also be used in a column default, so inserts do not have to mention it:

SQL
CREATE TABLE orders (
  order_id   NUMBER DEFAULT order_seq.NEXTVAL PRIMARY KEY,
  ordered_on DATE   DEFAULT SYSDATE
);

In PL/SQL 11g and later, you may use := order_seq.NEXTVAL directly; before 11g you had to SELECT order_seq.NEXTVAL INTO v FROM dual.

Sequence options

Option Meaning
START WITH n First value
INCREMENT BY n Step; may be negative for a descending sequence
MINVALUE, MAXVALUE Limits; the default maximum is 28 digits
CYCLE / NOCYCLE Wrap around after the limit, or raise an error
CACHE n / NOCACHE Pre-allocate n numbers in memory (default CACHE 20)
ORDER / NOORDER Guarantee numbers in request order (matters in RAC)
SESSION / GLOBAL (18c+) Session-private sequences for temporary data

Change a running sequence with ALTER SEQUENCE order_seq INCREMENT BY 10;. To restart at a value, 18c and later offer ALTER SEQUENCE order_seq RESTART START WITH 1;. DROP SEQUENCE order_seq; removes it.

Gaps are normal

Sequences do not promise gap-free numbers. A rolled-back insert still used its number. A cached block of numbers is lost when the instance restarts. Do not use a sequence for invoice numbers that the law requires to be consecutive; use a dedicated counter table with a lock instead. For a plain key that has no meaning, gaps do not matter.

Identity columns

An identity column declares the auto-numbering in the table itself:

SQL
CREATE TABLE customers (
  customer_id  NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name         VARCHAR2(100) NOT NULL
);

INSERT INTO customers (name) VALUES ('Ada Lovelace');

Three flavors control what happens if you supply a value yourself:

Clause You can insert an explicit value?
GENERATED ALWAYS AS IDENTITY No: ORA-32795: cannot insert into a generated always identity column
GENERATED BY DEFAULT AS IDENTITY Yes. If you omit the column, Oracle generates it.
GENERATED BY DEFAULT ON NULL AS IDENTITY Yes, and an explicit NULL also becomes a generated value

You can set the sequence options inline: GENERATED ALWAYS AS IDENTITY (START WITH 100 INCREMENT BY 10 CACHE 50). Behind the scenes Oracle creates a system-named sequence, which you can see in USER_SEQUENCES.

Identity columns are the better default for new tables: the key and its generator cannot drift apart, and loading data cannot forget to call NEXTVAL. Use a standalone sequence when several tables share one number series, or when you need NEXTVAL in application code first (for example, to generate a parent key before inserting the children).

flowchart LR
  A["INSERT without key"] --> B{"Key source"}
  B -->|"identity column"| C["Hidden sequence<br/>fills the column"]
  B -->|"DEFAULT seq.NEXTVAL"| D["Named sequence<br/>fills the column"]
  B -->|"explicit seq.NEXTVAL"| E["Your code asks<br/>for the number"]

Three ways a key value reaches a new row.

Reading the generated key back

After inserting, you often need the new id. In PL/SQL use RETURNING:

PLSQL
DECLARE
  v_id customers.customer_id%TYPE;
BEGIN
  INSERT INTO customers (name) VALUES ('Grace Hopper')
  RETURNING customer_id INTO v_id;
  DBMS_OUTPUT.PUT_LINE('Created customer ' || v_id);
END;
/

Never use SELECT MAX(customer_id) FROM customers to find the new id: another session may have inserted a row meanwhile.

Stand-ins you can run

SQLite's INTEGER PRIMARY KEY AUTOINCREMENT fills the key automatically, like an identity column:

SQL Auto-numbered key (stand-in for GENERATED AS IDENTITY)

Press Run (or Ctrl + Enter) to see the result here.

Gaps appear here too. A deleted row's number is not reused:

SQL Numbers are not reused

Press Run (or Ctrl + Enter) to see the result here.

The ids are 1, 2 and 4, just like an Oracle sequence after a rollback.

Finally, NEXTVAL/CURRVAL in a table of your own: a sequence behaves like a one-row counter. This is a stand-in only, because a real sequence needs no locking:

SQL A counter table imitating NEXTVAL

Press Run (or Ctrl + Enter) to see the result here.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. When can you call my_seq.CURRVAL without an error?

    CURRVAL returns the value this session last fetched with NEXTVAL. Before that, Oracle raises ORA-08002.
  2. 2. Which clause forbids you from supplying your own value for the key column?

    GENERATED ALWAYS rejects explicit values. BY DEFAULT accepts them, and ON NULL also replaces an explicit NULL.
  3. 3. A transaction calls NEXTVAL and then rolls back. What happens to the number?

    Sequences are not transactional. The number is consumed, so gaps are normal.
  4. 4. Which is the best way to get the key of a row you just inserted?

    RETURNING gives the exact value for your row. MAX(id) can return another session's row.

Summary

  • A sequence is a separate number generator used with NEXTVAL and CURRVAL.
  • GENERATED ... AS IDENTITY hides a sequence inside the table and is the best default for new keys.
  • ALWAYS, BY DEFAULT and BY DEFAULT ON NULL decide whether you may supply values yourself.
  • Sequences have gaps; never rely on them being consecutive.
  • Use RETURNING INTO to read a new key back safely.

Next: String Functions in Oracle