Oracle Sequences and Identity Columns: Generating Unique Keys

Every Table Needs a Key Strategy

Generating unique primary key values is a fundamental requirement for every database table. Oracle provides two main approaches: sequences (available since Oracle 7) and identity columns (added in Oracle 12c). Understanding the differences helps you choose the right approach and avoid common pitfalls.

Classic Sequences

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

-- Using in DML
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (emp_seq.NEXTVAL, 'John', 'Smith');

-- In PL/SQL (12c+ allows direct assignment)
DECLARE
    l_id NUMBER := emp_seq.NEXTVAL;
BEGIN
    INSERT INTO employees (employee_id, first_name) VALUES (l_id, 'Jane');
END;

The CACHE clause is important for performance. Without caching, each NEXTVAL call writes to the data dictionary, creating contention under high insert volumes. A cache of 20 is the default; for high-volume tables, increase it to 100 or 1000.

Identity Columns (12c and Later)

Identity columns embed the sequence behavior directly in the column definition:

CREATE TABLE orders (
    order_id   NUMBER GENERATED ALWAYS AS IDENTITY,
    order_date DATE NOT NULL,
    customer_id NUMBER NOT NULL
);

-- Insert without specifying the ID
INSERT INTO orders (order_date, customer_id) VALUES (SYSDATE, 100);

-- GENERATED BY DEFAULT allows explicit values (useful for data migration)
CREATE TABLE products (
    product_id NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1000),
    product_name VARCHAR2(200)
);

GENERATED ALWAYS vs GENERATED BY DEFAULT

GENERATED ALWAYS means the database always generates the value and rejects any attempt to insert or update the column explicitly. This is the strictest option. GENERATED BY DEFAULT generates a value only when one is not provided, allowing explicit values during data migration or testing. GENERATED BY DEFAULT ON NULL generates a value when the supplied value is NULL, which is useful when the insert statement includes the column but the value might be NULL.

Sequences vs Identity: When to Use Which

Use identity columns for new tables where the primary key is a simple auto-incrementing number. They are cleaner, self-documenting, and eliminate the need to manage a separate sequence object. Use explicit sequences when you need to share the same sequence across multiple tables, when you need to reference the sequence value before the insert, or when you are working with legacy code that already uses sequences.

Gaps in Sequence Values

Sequence values are not guaranteed to be contiguous. Rollbacks, cached values lost during instance restarts, and RAC environments all create gaps. Never rely on sequence values being gap-free. If you need a gap-free numbering system, such as for invoice numbers, implement it separately using a serialized counter table with row-level locking.