Sequences & Identity Columns

Every table in both schemas has a numeric primary key. So far those keys have been inserted manually — which works in examples but fails in real applications where two sessions inserting simultaneously would collide on the same value. Oracle solves this with sequences: independent database objects that generate a guaranteed unique number each time they are called, regardless of how many sessions are running concurrently. This lesson covers creating and using sequences, the NEXTVAL and CURRVAL pseudocolumns, and Oracle 12c's identity column syntax which wraps a sequence directly into a column definition.

To follow along with the examples in this lesson, use the course dataset from Lesson 8.

Creating a Sequence

A sequence is a schema object defined once and then called whenever a new unique number is needed. It has no direct relationship to any table — the same sequence could theoretically supply values to multiple tables, though in practice each table gets its own sequence for clarity. The key parameters are START WITH, INCREMENT BY, MINVALUE, MAXVALUE, and whether it cycles or caches values.

CACHE is one of the most important performance parameters. Oracle pre-allocates a block of sequence numbers in memory — by default 20. When a session requests NEXTVAL Oracle returns the next cached number instantly without a disk read. If the database restarts unexpectedly the cached numbers are lost and a gap appears in the sequence. Gaps are normal and expected — a sequence guarantees uniqueness, not consecutiveness.

-- Basic sequence for customers — starts at 1, increments by 1
CREATE SEQUENCE seq_customer_id
START WITH     1
INCREMENT BY   1
MINVALUE       1
MAXVALUE       9999999999
NOCYCLE        -- when MAXVALUE is reached raise an error rather than wrapping back to MINVALUE
CACHE          20;

-- Sequence for orders — starts at 5001 to match the dataset
CREATE SEQUENCE seq_order_id
START WITH     5001
INCREMENT BY   1
MINVALUE       5001
MAXVALUE       9999999999
NOCYCLE
CACHE          20;

-- View sequence definitions in the data dictionary
SELECT sequence_name,
       min_value,
       max_value,
       increment_by,
       cycle_flag,
       cache_size,
       last_number   -- next value Oracle will use when the cache is refreshed
FROM   user_sequences
WHERE  sequence_name IN ('SEQ_CUSTOMER_ID', 'SEQ_ORDER_ID')
ORDER BY sequence_name;

• last_number in user_sequences shows the next value Oracle will allocate when the cache block is replenished — it jumps by the cache size each time, so with CACHE 20 it increments by 20 after each cache refresh, not by 1
• NOCYCLE is the right choice for primary keys — if a sequence reaches its maximum and cycles back to 1 it would generate values that already exist as primary keys, causing constraint violations
• Gaps in sequence values are normal — they occur on database restarts (cached values lost), rolled-back transactions (the NEXTVAL was consumed and is not returned), and RAC environments; never design business logic that assumes sequence values are consecutive

Using NEXTVAL and CURRVAL

sequence_name.NEXTVAL increments the sequence and returns the new value. sequence_name.CURRVAL returns the value most recently generated by NEXTVAL in the current session — it does not increment the sequence again. CURRVAL raises an error if NEXTVAL has not been called at least once in the current session.

Both pseudocolumns can be used in SELECT statements (querying from DUAL), in INSERT statements, and in UPDATE statements. They cannot be used in WHERE clauses, GROUP BY, ORDER BY, or subqueries inside SELECT.

-- Call NEXTVAL to get the next sequence value
SELECT seq_customer_id.NEXTVAL FROM DUAL;

-- CURRVAL returns the last value generated in this session — does not increment
SELECT seq_customer_id.CURRVAL FROM DUAL;

-- Use NEXTVAL directly inside an INSERT statement
INSERT INTO customers (customer_id, full_name, email, country, loyalty_tier)
VALUES (seq_customer_id.NEXTVAL, 'Elena Vasquez', 'elena@example.com', 'Spain', 'standard');

-- Insert a related order using CURRVAL to get the customer_id just generated
-- and NEXTVAL on the order sequence for the order_id
INSERT INTO orders (order_id, customer_id, status, total_amount)
VALUES (seq_order_id.NEXTVAL, seq_customer_id.CURRVAL, 'pending', 0);

-- Confirm both rows were created with the correct IDs
SELECT c.customer_id, c.full_name, o.order_id, o.status
FROM   customers c
JOIN   orders    o ON o.customer_id = c.customer_id
WHERE  c.email = 'elena@example.com';

• CURRVAL is session-scoped — two concurrent sessions each calling NEXTVAL receive different values, and each session's CURRVAL reflects only its own last NEXTVAL call; this makes it safe to use CURRVAL in multi-user environments
• The customer_id in the output is 2 because NEXTVAL was called once in the SELECT above before the INSERT — this is a common source of confusion; in production always call NEXTVAL only inside the INSERT itself
• NEXTVAL from DUAL is used in examples and testing — in application code the sequence is typically called directly inside the INSERT without a separate SELECT

Creating Sequences for Both Schemas

Each table with a numeric primary key gets its own sequence. The naming convention used throughout the course is seq_columnname — making it unambiguous which sequence feeds which column.

-- DataplexaStore sequences
CREATE SEQUENCE seq_customer_id  START WITH 1    INCREMENT BY 1 NOCYCLE CACHE 20;
CREATE SEQUENCE seq_product_id   START WITH 1    INCREMENT BY 1 NOCYCLE CACHE 20;
CREATE SEQUENCE seq_order_id     START WITH 5001 INCREMENT BY 1 NOCYCLE CACHE 20;
CREATE SEQUENCE seq_order_item_id START WITH 1   INCREMENT BY 1 NOCYCLE CACHE 20;
CREATE SEQUENCE seq_payment_id   START WITH 1    INCREMENT BY 1 NOCYCLE CACHE 20;

-- DataplexaHR sequences
CREATE SEQUENCE seq_department_id  START WITH 10  INCREMENT BY 10 NOCYCLE CACHE 20;
CREATE SEQUENCE seq_employee_id    START WITH 100 INCREMENT BY 1  NOCYCLE CACHE 20;
CREATE SEQUENCE seq_salary_id      START WITH 1   INCREMENT BY 1  NOCYCLE CACHE 20;
CREATE SEQUENCE seq_payroll_id     START WITH 1   INCREMENT BY 1  NOCYCLE CACHE 20;

-- jobs uses a VARCHAR2 primary key (e.g. 'IT_PROG') — no sequence needed

-- Verify all sequences were created
SELECT sequence_name, min_value, increment_by, cache_size
FROM   user_sequences
ORDER BY sequence_name;

• seq_department_id increments by 10 — departments are a small, slowly growing list and gaps of 10 between IDs leave room to insert a department between two existing ones in reporting if needed
• jobs has a VARCHAR2 primary key — sequences only generate numbers, so a readable code like IT_PROG is assigned manually and a sequence would add no value
• Sequence start values should reflect the data already in the table — if the dataset already contains customers 1 through 12, the sequence must start at 13 or higher to avoid primary key collisions on the first insert

Identity Columns (Oracle 12c and Later)

Oracle 12c introduced identity columns — a way to attach auto-increment behaviour directly to a column definition without creating a separate sequence object. Oracle creates and manages the underlying sequence automatically. Identity columns come in three variants: GENERATED ALWAYS (Oracle always generates the value — you cannot supply your own), GENERATED BY DEFAULT (Oracle generates a value if you do not provide one), and GENERATED BY DEFAULT ON NULL (Oracle generates a value if you provide NULL).

-- Identity column with GENERATED ALWAYS
-- Oracle generates the ID on every insert — supplying a value raises ORA-32795
CREATE TABLE product_reviews (
    review_id    NUMBER        GENERATED ALWAYS AS IDENTITY,
    product_id   NUMBER(10)    NOT NULL,
    rating       NUMBER(2,1)   NOT NULL,
    review_text  VARCHAR2(500),
    review_date  DATE          DEFAULT SYSDATE NOT NULL,
    CONSTRAINT pk_product_reviews PRIMARY KEY (review_id)
);

-- Insert without specifying review_id — Oracle fills it automatically
INSERT INTO product_reviews (product_id, rating, review_text)
VALUES (1, 4.5, 'Great headphones, very comfortable.');

INSERT INTO product_reviews (product_id, rating, review_text)
VALUES (1, 3.0, 'Good but battery life could be better.');

SELECT review_id, product_id, rating FROM product_reviews;

-- Identity column with GENERATED BY DEFAULT — allows supplying your own value
-- Useful during data migrations where you need to preserve existing IDs
CREATE TABLE product_reviews_v2 (
    review_id    NUMBER        GENERATED BY DEFAULT AS IDENTITY START WITH 100,
    product_id   NUMBER(10)    NOT NULL,
    rating       NUMBER(2,1)   NOT NULL,
    CONSTRAINT pk_reviews_v2 PRIMARY KEY (review_id)
);

-- Explicit ID supplied — Oracle accepts it when GENERATED BY DEFAULT is used
INSERT INTO product_reviews_v2 (review_id, product_id, rating) VALUES (999, 2, 5.0);

-- No ID supplied — Oracle uses the sequence
INSERT INTO product_reviews_v2 (product_id, rating) VALUES (3, 4.0);

• GENERATED ALWAYS is the safest choice for new tables — it makes the primary key completely hands-off and prevents accidental manual ID insertion that could clash with the sequence
• GENERATED BY DEFAULT is useful during migrations where existing IDs need to be preserved alongside new auto-generated ones — use it only when you have a specific reason to supply values manually
• The underlying sequence Oracle creates for an identity column can be inspected in user_sequences — it appears with a system-generated name like ISEQ$$_12345

Altering and Dropping Sequences

Sequences can be modified after creation with ALTER SEQUENCE. The only parameter that cannot be changed is START WITH — to reset a sequence to a different starting point you must drop and recreate it. All other parameters including INCREMENT BY, MAXVALUE, CACHE, and CYCLE can be altered.

-- Increase the cache size for a high-volume sequence
ALTER SEQUENCE seq_order_item_id CACHE 100;

-- Change increment — useful for resetting a sequence to jump past existing values
-- This advances the sequence by 1000 on the next NEXTVAL call
ALTER SEQUENCE seq_order_id INCREMENT BY 1000;
SELECT seq_order_id.NEXTVAL FROM DUAL;  -- returns 6001 if current value was 5001
ALTER SEQUENCE seq_order_id INCREMENT BY 1;  -- reset increment back to 1

-- Drop a sequence permanently
DROP SEQUENCE seq_payment_id;

-- Recreate with a corrected start value
CREATE SEQUENCE seq_payment_id
START WITH     100
INCREMENT BY   1
NOCYCLE
CACHE          20;

• The increment jump trick — temporarily setting INCREMENT BY to a large number, calling NEXTVAL once, then resetting — is the standard way to advance a sequence past a gap without dropping and recreating it
• Dropping a sequence does not affect any table data — rows that already have values generated by the sequence keep those values; only future NEXTVAL calls are affected
• If a sequence is dropped that backs an identity column, Oracle raises an error — identity column sequences are managed by Oracle and cannot be dropped independently

Summary

Practice Questions

Quiz

Next up — Inserting Data — Learn how to add rows to the DataplexaStore and DataplexaHR tables using single-row inserts, multi-row inserts, and INSERT INTO SELECT.