How Sequences Work (And How to Use Them)

WORKING WITH ORACLE PRIMARY KEYS

If you're coming from MySQL or PostgreSQL, generating a primary key in Oracle takes a bit of getting used to. Instead of an AUTO_INCREMENT or SERIAL column, Oracle hands that job to a separate counter object called a sequence.

1. What is an Oracle sequence?

A sequence is a counter that lives in your database and hands out unique numbers whenever you ask for one. It's not attached to any particular table. You set it up once, then pull numbers from it anywhere you need a unique ID, whether that's for a customer_id, an order_id, or something else entirely.

2. Creating your first sequence

A few lines of SQL are all it takes:

SQL

CREATE SEQUENCE cust_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999999
NOCYCLE
CACHE 20;

A quick rundown of what those settings actually mean:

Option	What it does
START WITH	The first number the sequence will hand out.
INCREMENT BY	How much the counter jumps with each new number.
MINVALUE / MAXVALUE	The lowest and highest values the sequence is allowed to reach.
NOCYCLE	Keeps the sequence from wrapping back around to 1 once it hits MAXVALUE.
CACHE 20	Loads 20 numbers into memory ahead of time so inserts run faster.

You can double check the sequence actually got created by querying the data dictionary:

SQL

SELECT sequence_name, last_number
FROM user_sequences
WHERE sequence_name = 'CUST_SEQ';

3. Testing the sequence

There are two pseudo-columns you'll use to actually read values from a sequence:

• NEXTVAL: pulls the next number in line and moves the counter forward.
• CURRVAL: shows the last number your session generated. It won't work until you've called NEXTVAL at least once.

SQL

SELECT cust_seq.NEXTVAL FROM dual;
-- Returns: 1

SELECT cust_seq.NEXTVAL FROM dual;
-- Returns: 2

SELECT cust_seq.CURRVAL FROM dual;
-- Returns: 2

4. Three ways to link a sequence to a table

Once the sequence exists, you still need to connect it to your table's primary key. Which route you take mostly comes down to which Oracle version you're running.

Option A: DEFAULT (best for Oracle 12c and newer)

This is the cleanest option if you're on a modern Oracle version. You just assign the sequence right in the table definition.

SQL

CREATE TABLE customers (
    customer_id   NUMBER DEFAULT cust_seq.NEXTVAL PRIMARY KEY,
    first_name    VARCHAR2(50),
    last_name     VARCHAR2(50),
    created_date  DATE DEFAULT SYSDATE
);

From here you can skip customer_id in your inserts altogether:

SQL

INSERT INTO customers (first_name, last_name)
VALUES ('Jane', 'Smith');

Option B: reference NEXTVAL directly in your INSERT

If there's no default set on the table, you'll need to call the sequence yourself every time you insert a row:

SQL

INSERT INTO customers (customer_id, first_name, last_name)
VALUES (cust_seq.NEXTVAL, 'John', 'Doe');

Option C: trigger-based (legacy, pre-12c)

On Oracle 11g and earlier, a column couldn't reference a sequence directly, so people used a BEFORE INSERT trigger instead. You'll still run into this in a lot of older codebases:

SQL

CREATE OR REPLACE TRIGGER trg_customer_id
BEFORE INSERT ON customers
FOR EACH ROW
WHEN (NEW.customer_id IS NULL)
BEGIN
    SELECT cust_seq.NEXTVAL
    INTO :NEW.customer_id
    FROM dual;
END;
/

5. Pros and cons

PROS Fast. Numbers are cached in memory, so Oracle doesn't need to lock tables or check existing rows to hand out the next value. Reusable. One sequence can feed several tables or scripts if you want it to. No contention. Multiple users can grab numbers at the same time without waiting on each other.

CONS Gaps happen. If an insert fails or a transaction rolls back, that number is gone for good. Cache loss on restart. Any pre-cached numbers that weren't used yet disappear if the database restarts unexpectedly. One more object to manage. Sequences exist independently of your tables, so you have to track and drop them separately.

6. Complete working example

Here's a full script you can copy, paste, and run as is:

SQL

-- 1. Create sequence
CREATE SEQUENCE cust_seq
START WITH 100
INCREMENT BY 1
NOCYCLE
CACHE 20;

-- 2. Create table with sequence default
CREATE TABLE customers (
    customer_id   NUMBER DEFAULT cust_seq.NEXTVAL PRIMARY KEY,
    first_name    VARCHAR2(50) NOT NULL,
    last_name     VARCHAR2(50) NOT NULL,
    created_date  DATE DEFAULT SYSDATE
);

-- 3. Add test rows
INSERT INTO customers (first_name, last_name) VALUES ('Alice', 'Brown');
INSERT INTO customers (first_name, last_name) VALUES ('Bob', 'Green');

-- 4. View results
SELECT * FROM customers;

Which gives you:

CUSTOMER_ID	FIRST_NAME	LAST_NAME	CREATED_DATE
100	Alice	Brown	current date
101	Bob	Green	current date

---

Summary

• Oracle sequences are fast, standalone counters. They're not tied to any one table by default.
• You can link one to a table with NEXTVAL in an INSERT, a DEFAULT clause on 12c and up, or a trigger on older versions.
• For anything modern, DEFAULT cust_seq.NEXTVAL is the simplest way to get clean, auto-incrementing keys without writing a trigger.