Oracle Sequences and Identity Columns

by

Oracle sequences generate numeric values independently of a table and are commonly used for surrogate keys, batch identifiers, and other incrementing values.

Create and use a sequence

CREATE SEQUENCE supplier_seq
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

INSERT INTO suppliers (supplier_id, supplier_name)
VALUES (supplier_seq.NEXTVAL, 'Example');

NEXTVAL gets the next value. Gaps are normal and should be expected, especially with caching, rollbacks, or instance restarts.

Identity columns

In newer Oracle versions, an identity column is often simpler when the sequence exists only to populate one table’s key:

CREATE TABLE suppliers (
  supplier_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
  supplier_name VARCHAR2(100)
);

Use a standalone sequence when several objects need to share the same number generator or when you need explicit control over sequence behaviour.

Leave a Comment