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;

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;
/

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

INSERT INTO my_table (id, column1) VALUES (my_table_seq.NEXTVAL, 'value1');

---

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.

Comments

[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...