NUHMAN.com

In Oracle, unlike MySQL or PostgreSQL, there’s no direct AUTO_INCREMENT keyword. But that doesn’t mean we can’t achieve the same effect. Instead, Oracle uses a combination of sequences and triggers to create auto-incrementing primary keys.

Here’s a step-by-step walkthrough on how to make an id column auto-increment in PL/SQL.

🧱 Step 1: Create the Table (if it doesn't exist)

Let’s say we want to auto-increment the id column in the my_table:

sql

CREATE TABLE my_table (
    id NUMBER,
    name VARCHAR2(50)
);

Now, let’s make sure id is the primary key:

sql

ALTER TABLE my_table ADD CONSTRAINT my_table_pk PRIMARY KEY (id);

🔁 Step 2: Create a Sequence

A sequence is an object that generates unique numbers—perfect for our use case.

sql

CREATE SEQUENCE my_table_seq
START WITH 1 -- or use a custom value
INCREMENT BY 1
NOCACHE
NOCYCLE;

If your table already contains data, you should find the highest ID and start from the next number:

sql

SELECT MAX(id) FROM my_table;

-- Drop and recreate the sequence starting from MAX + 1
DROP SEQUENCE my_table_seq;

CREATE SEQUENCE my_table_seq
START WITH 101 -- if MAX(id) = 100
INCREMENT BY 1
NOCACHE
NOCYCLE;

⚙️ Step 3: Create a Trigger for Auto-Increment

Now comes the magic—the trigger. This will run before each insert and automatically assign the next sequence value to the id column if it's not already set.

sql

CREATE OR REPLACE TRIGGER my_table_before_insert
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
    IF :NEW.id IS NULL THEN
        SELECT my_table_seq.NEXTVAL
        INTO :NEW.id
        FROM dual;
    END IF;
END;
/

This ensures that even if you insert a row without specifying id, the trigger will handle it for you.

🧪 Step 4: Insert Without the ID

Now you can insert data without worrying about the primary key:

sql

INSERT INTO my_table (name) VALUES ('John Doe');
INSERT INTO my_table (name) VALUES ('Jane Smith');

And Oracle will handle the id values automatically.

✅ Summary

To auto-increment a primary key in Oracle PL/SQL:

1. Create a sequence to generate the IDs.
2. Create a trigger to assign the next sequence value before insert.
3. Set the column as primary key (if not already).
4. Optionally adjust the sequence to continue from the current max ID if data already exists.

🔁 Bonus: For Oracle 12c+

If you’re using Oracle 12c or later, you can use identity columns to simplify this:

sql

CREATE TABLE my_table (
    id NUMBER GENERATED ALWAYS AS IDENTITY,
    name VARCHAR2(50),
    PRIMARY KEY (id)
);

No sequence or trigger needed!

This approach gives you MySQL-style auto-increment behavior in Oracle. It's a reliable and flexible way to manage primary keys without manual intervention.