CREATE TABLE in Oracle with Auto Increment ID

If you have worked with MySQL or SQL Server, you are probably used to AUTO_INCREMENT or IDENTITY columns that generate unique IDs automatically. Oracle does things a little differently, but once you understand how it works, it becomes just as easy. This guide walks you through everything you need to know about creating a table in Oracle with an auto-incrementing primary key.

Why Auto Increment IDs Matter

Every table needs a way to uniquely identify each row. An auto-increment ID takes that job off your hands. You do not have to manually track the last number used or worry about duplicate keys.

As database experts often say, "A reliable primary key is the foundation of a solid data model." When you let the database generate the key, you reduce the chances of human error and keep your inserts clean and simple.

How Oracle Handles Auto Increment

Oracle did not support a built-in auto-increment column syntax until Oracle 12c. Before that, you had to use a sequence combined with a trigger to simulate the behavior. Now you have two solid options depending on the version of Oracle you are running.

Using the IDENTITY Column (Oracle 12c+)

Starting with Oracle 12c, you can define a column as a generated identity column right inside your CREATE TABLE statement. This is the cleanest and most modern approach.

Here is the basic syntax:

CREATE TABLE employees (
    employee_id  NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name   VARCHAR2(50),
    last_name    VARCHAR2(50),
    hire_date    DATE
);

The GENERATED ALWAYS clause means Oracle will always generate the value. You cannot manually insert a value into that column, which keeps your data consistent.

GENERATED ALWAYS vs GENERATED BY DEFAULT

Oracle gives you a couple of variations for the IDENTITY column. Here is how they compare:

If you want full control over when manual values are allowed, use GENERATED BY DEFAULT. If strict auto-generation is your goal, stick with GENERATED ALWAYS.

Customizing the Start Value and Increment

You do not have to start at 1 or increment by 1. Oracle lets you customize both values inside the IDENTITY definition.

CREATE TABLE orders (
    order_id    NUMBER GENERATED ALWAYS AS IDENTITY
                    (START WITH 1000 INCREMENT BY 5)
                    PRIMARY KEY,
    order_date  DATE,
    customer_id NUMBER
);

In this example, the first order ID will be 1000 and each new row will increment by 5. This is useful when you need IDs in a specific range or spacing.

Using a Sequence and Trigger (Oracle 11g and Earlier)

If you are working on an older Oracle database, you will need to create a sequence first and then use a BEFORE INSERT trigger to populate the ID column automatically.

Step 1: Create the Table

CREATE TABLE customers (
    customer_id  NUMBER PRIMARY KEY,
    full_name    VARCHAR2(100),
    email        VARCHAR2(150)
);

Step 2: Create the Sequence

CREATE SEQUENCE customers_seq
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

Step 3: Create the Trigger

CREATE OR REPLACE TRIGGER customers_bir
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
    IF :NEW.customer_id IS NULL THEN
        SELECT customers_seq.NEXTVAL
        INTO :NEW.customer_id
        FROM dual;
    END IF;
END;
/

With this setup, every time you insert a row without providing a customer_id, the trigger fires and pulls the next value from the sequence automatically.

Inserting Data Into Your Table

When you use an IDENTITY column or a trigger-based sequence, your INSERT statement becomes much simpler. You do not need to include the ID column at all.

-- For IDENTITY column (Oracle 12c+)
INSERT INTO employees (first_name, last_name, hire_date)
VALUES ('Sarah', 'Johnson', SYSDATE);

-- For sequence + trigger (Oracle 11g)
INSERT INTO customers (full_name, email)
VALUES ('Mike Davis', 'mike@example.com');

Oracle takes care of the rest. Each new row gets a unique ID without any extra work on your end.

How to Check the Current Sequence Value

If you are using a sequence, you may want to check the current value at any time. Use this query:

SELECT customers_seq.CURRVAL FROM dual;

Note that CURRVAL is only available after you have called NEXTVAL at least once in your session. If you need the last generated ID after an insert, this is your go-to query.

Common Mistakes to Avoid

When setting up auto increment in Oracle, there are a few pitfalls that catch a lot of developers off guard. Here is a quick list to keep in mind:

• Do not try to insert a value into a GENERATED ALWAYS column. Oracle will throw an error.
• Do not forget to include NOCACHE in your sequence if you are in a test environment and need exact sequential values.
• Do not share one sequence across multiple tables. Each table should have its own dedicated sequence.
• Do not skip the NOCYCLE option if you do not want the sequence to restart from the beginning after hitting the max value.

Verifying Your Table Structure

After creating your table, you can verify the structure using the DESCRIBE command or by querying the data dictionary.

-- Describe the table
DESCRIBE employees;

-- Check column details in the data dictionary
SELECT column_name, data_type, identity_column
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES';

The identity_column field will show YES for the column you configured as an IDENTITY column, confirming everything is set up correctly.

Dropping and Recreating a Table with Auto Increment

If you drop a table that uses a sequence, the sequence stays in the database. You need to drop it separately to keep your schema clean.

DROP TABLE customers;
DROP SEQUENCE customers_seq;

For IDENTITY columns, dropping the table removes the internal sequence automatically. You do not have to do anything extra.

Conclusion

Setting up an auto-increment ID in Oracle is straightforward once you know which version of Oracle you are working with. If you are on Oracle 12c or later, the IDENTITY column syntax is the cleanest and most efficient option. If you are on an older version, the sequence and trigger pattern gives you the same result with just a few extra steps.

Whether you go with an IDENTITY column or a traditional sequence, the goal is the same: let Oracle handle unique ID generation so you can focus on building your application logic. Take the approach that fits your environment, and your tables will be set up with a solid foundation from day one.