How to Replace AUTO_INCREMENT in Oracle SQL Using Sequences and Triggers

Oracle does not support an AUTO_INCREMENT keyword as found in some other relational databases (such as MySQL). Instead, Oracle uses SQL sequences in combination with either direct insertion calls or triggers to achieve similar functionality for automatically generating primary key values. Here is a detailed explanation of how sequences are used:

What is a Sequence in Oracle?

A sequence is an independent database object in Oracle that can generate a series of numeric values according to rules you define. A sequence can increment its value by a specified amount each time it’s accessed, and it can cycle back to a specified start value if needed. Once created, a sequence is not directly tied to any specific table. Instead, you reference it whenever you need a unique number, often for use as a primary key.

Creating a Sequence

You use the CREATE SEQUENCE statement to define a new sequence. The basic syntax is:

CREATE SEQUENCE sequence_name
  START WITH 1
  INCREMENT BY 1
  CACHE 20;  -- Optional performance optimization

Explanation:

• sequence_name: A unique name for your sequence.
• START WITH: The number at which the sequence starts generating values.
• INCREMENT BY: The value by which the sequence number increments. Typically this is 1 for generating consecutive integers.
• CACHE: How many sequence values to pre-allocate and keep in memory for faster access. Optional but often used for performance.

Generating Values from a Sequence

Once a sequence is created, you can refer to two “pseudo-columns” from it:

• sequence_name.NEXTVAL: Returns the next number in the sequence and increments the sequence.
• sequence_name.CURRVAL: Returns the current value of the sequence in the session, if one has been fetched.

The most common usage pattern is to call NEXTVAL whenever you need a new primary key:

INSERT INTO my_table (id, name, description)
VALUES (my_sequence.NEXTVAL, 'Sample Name', 'Sample Description');

Using a Sequence in a Trigger for “Auto-Increment” Behavior

To simulate the convenience of AUTO_INCREMENT behavior, you can write a BEFORE INSERT trigger on your table. This way, when you insert a row without specifying the primary key, the trigger automatically fetches the next sequence value and assigns it to the primary key column.

Example Trigger:

CREATE OR REPLACE TRIGGER my_table_bir
BEFORE INSERT ON my_table
FOR EACH ROW
WHEN (NEW.id IS NULL)
BEGIN
    :NEW.id := my_sequence.NEXTVAL;
END;

What this does:

• The trigger fires before each insert into my_table.
• If no value is provided for the id column, the trigger sets :NEW.id to the next value from my_sequence.
• The insert statement does not need to include the id, mimicking the convenience of an auto-increment behavior.

Using Identity Columns (Oracle 12c and Later)

Starting from Oracle 12c, you can also create a table with an identity column, which simplifies usage and acts more like an auto-incrementing column:

CREATE TABLE my_table (
    id NUMBER GENERATED ALWAYS AS IDENTITY,
    name VARCHAR2(100),
    description VARCHAR2(200)
);

With this approach, you don’t need to explicitly create a sequence or a trigger—Oracle handles it internally. Each insert without specifying id will automatically generate a new value.

Final Thoughts

• Before Oracle 12c: Use a sequence plus either direct calls to NEXTVAL in insert statements or use a trigger to populate a primary key field automatically.
• Oracle 12c and Beyond: Leverage identity columns for more seamless “auto-increment” functionality without manually handling sequences and triggers.

In all cases, sequences are the underlying mechanism Oracle provides to ensure unique, incrementally increasing numeric values, effectively replacing the functionality of an AUTO_INCREMENT keyword.