DEV Community

Pranav Bakare
Pranav Bakare

Posted on

How Sequences used in Oracle SQL without AUTO_INCREMENT Keyword

AUTO_INCREMENT in MySQL and Sequence in Oracle SQL

In Oracle SQL, there is no AUTO_INCREMENT keyword like in MySQL. This is because Oracle handles auto-increment functionality differently using sequences and optionally triggers. While MySQL has the AUTO_INCREMENT keyword to automatically generate unique numeric values, Oracle relies on the flexibility of sequences to achieve the same outcome.


Why Oracle Doesn't Use AUTO_INCREMENT

Oracle focuses on database objects like sequences to provide more granular control over value generation.

Using sequences, you can:

  • Set custom starting values.
  • Define the increment step.
  • Manage caching or cycling of values.
  • This makes sequences more versatile than the fixed AUTO_INCREMENT approach.

How Oracle Implements Auto-Increment Without AUTO_INCREMENT

Instead of using an AUTO_INCREMENT keyword, Oracle uses the following steps:

1. Create a Sequence: Defines how unique values are generated.

CREATE SEQUENCE my_table_seq
START WITH 1 INCREMENT BY 1 NOCYCLE;
Enter fullscreen mode Exit fullscreen mode

2. Use a Trigger (Optional): Automates the assignment of sequence values to a column.

CREATE OR REPLACE TRIGGER my_table_trigger
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
  :NEW.id := my_table_seq.NEXTVAL;
END;
/
Enter fullscreen mode Exit fullscreen mode

3. Direct Sequence Usage: Insert rows by explicitly calling the sequence.

INSERT INTO my_table (id, column1) VALUES (my_table_seq.NEXTVAL, 'value1');
Enter fullscreen mode Exit fullscreen mode

Key Takeaway

  • The AUTO_INCREMENT keyword does not exist in Oracle SQL. Instead:
  • Use sequences to generate unique identifiers.
  • Optionally, automate this with triggers for convenience.

This approach provides flexibility while achieving functionality similar to AUTO_INCREMENT.

Image of Timescale

πŸš€ pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applicationsβ€”without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more β†’

Top comments (1)

Collapse
 
databasesponge profile image
MetaDave πŸ‡ͺπŸ‡Ί β€’

Since Oracle 12C, released in 2014, a sequence can be specified as the default value for a column. I doubt anyone is writing new code with triggers to add sequence values.

12C also introduced identity columns: NUMBER GENERATED ALWAYS AS IDENTITY, which use sequences behind the scenes.

oracle-base.com/articles/12c/ident...

Image of Docusign

πŸ› οΈ Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more