Auto-increment in Oracle without a trigger: Identity Columns

When I teach table design, students who have used MySQL often ask:

“MySQL has AUTO_INCREMENT. How do we do that in Oracle?”

My answer used to be: “Oracle doesn't have an auto-increment data type. We use a database object called a sequence to supply the numbers.”

Since Oracle 12c, the job has become much easier.

An example scenario

We're designing an employee table. Every row needs a unique identifier that other tables can reference. We call that column the primary key.

Where should its numbers come from? If people enter them by hand, sooner or later someone will repeat or forget one. We want the database to assign them: 1 for the first row, 2 for the next, and so on.

The old approach: assemble two pieces yourself

Before 12c, we first created a sequence. Think of it as Oracle's number dispenser: each call returns the next number. It operates independently of the table and doesn't even know our table exists.

Then we needed a way to put that number into the table during an insert. That's where a trigger came in: code that runs automatically when a particular event occurs, in this case whenever a new row arrives.

CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER emp_bir
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  :NEW.emp_id := emp_seq.NEXTVAL;
END;
/

This works. There's nothing inherently wrong with it. But imagine a system with 50 tables: now we have 50 sequences and another 50 triggers scattered through the database. During a migration, or when tracing where an identifier comes from, we have to track them all down.

The newer approach: declare it with the table

From 12c onward, we can declare during CREATE TABLE that the database should generate values for a column.

CREATE TABLE employees (
  emp_id   NUMBER GENERATED ALWAYS AS IDENTITY,
  emp_name VARCHAR2(100)
);

INSERT INTO employees (emp_name) VALUES ('สมชาย');
INSERT INTO employees (emp_name) VALUES ('สมหญิง');

SELECT * FROM employees;

--   EMP_ID  EMP_NAME
--        1  สมชาย
--        2  สมหญิง

Notice that the inserts don't supply emp_id, but each row still gets a number. The Thai names in this example are retained from the original article.

Behind the scenes, Oracle still uses a sequence. It simply creates one automatically, with a name beginning ISEQ$$_, and associates it with the column. When we drop the table, that sequence goes with it. We don't leave an old object behind.

Choose the mode that matches the job

Identity columns offer three modes. The difference is how much freedom we have to supply our own values.

GENERATED ALWAYS always generates the number. An explicit value is rejected. This suits a primary key in a new system where we don't want applications supplying identifiers.

GENERATED BY DEFAULT generates a value when we omit one, but accepts a value we supply. This is useful when importing existing data that already has identifiers.

GENERATED BY DEFAULT ON NULL works like BY DEFAULT, but also generates a number when an application explicitly supplies NULL. This can help with an older application whose code we can no longer change.

We can configure the usual sequence options too. For example, start at 1000 and increment by 10:

emp_id NUMBER GENERATED BY DEFAULT AS IDENTITY
       (START WITH 1000 INCREMENT BY 10 CACHE 50)

Is ALWAYS really that strict?

Students sometimes ask whether “you can't supply a value” is an actual restriction. Let's try it.

INSERT INTO employees (emp_id, emp_name) VALUES (99, 'สมปอง');

-- ORA-32795: cannot insert into a generated always identity column

Rejected immediately.

I like this mode because the database enforces the rule. We don't have to rely on every programmer remembering it. A system that depends entirely on human discipline will eventually encounter a mistake.

Find the tables that use identity columns

As a system grows, we can't remember every detail. There's no need to inspect tables one at a time: Oracle keeps this information in its data dictionary.

SELECT table_name, column_name, generation_type, sequence_name
FROM   user_tab_identity_cols;

-- TABLE_NAME  COLUMN_NAME  GENERATION_TYPE  SEQUENCE_NAME
-- EMPLOYEES   EMP_ID       ALWAYS           ISEQ$$_73542

generation_type tells us which mode is in use. sequence_name identifies the sequence Oracle created. That's useful when investigating how far the numbering has progressed.

After importing data, we can set where numbering should resume through ALTER TABLE, rather than modifying the sequence directly.

ALTER TABLE employees
MODIFY emp_id GENERATED BY DEFAULT AS IDENTITY (START WITH 5001);

Three common pitfalls

First, numbers can have gaps, and that's normal. If an insert is rolled back, the number it used isn't returned. Cached numbers may also be lost when the database is restarted.

Don't use an identity column as a gap-free invoice or document numbering system where consecutive numbering is required. That needs a separate design.

Second, an existing ordinary column cannot simply be converted directly into an identity column. Plan the migration: create a new column, move the data, and switch over.

Third, when importing into a BY DEFAULT table with Data Pump, check the underlying sequence. If existing identifiers are higher than its next value, a new insert may collide with an existing row and raise ORA-00001, a duplicate-value error. Use ALTER TABLE to restart above the existing identifiers.

Must we replace every existing trigger?

If the current production system works well, there's no need to rush. Changing 50 tables at once can create more risk than benefit. My recommendation is to use identity columns for new tables, and convert older ones when you already need to work on them, such as during a major refactor or version migration. Test the changes together.

Another frequent question is whether this can make inserts faster. A row-level trigger makes Oracle switch between SQL and PL/SQL processing for each row. Small jobs may not reveal much difference, but a batch inserting hundreds of thousands of rows can make that overhead noticeable. Identity generation removes that trigger step by operating within the engine.

Summary

On Oracle 12c or later, you don't need to write a sequence-and-trigger pair just to generate identifiers. Declare GENERATED AS IDENTITY when creating the table, choose the mode that fits the job, and remember that values can have gaps.

I write about Oracle and SQL every week, from short tips to common practical problems. You can follow my teeDBA Facebook page.