Oracle IDENTITY Column for Auto-Incrementing IDs

Creating unique primary keys automatically is a frequent need in Oracle database development. Traditionally, this required manual setup using sequences and triggers. Starting from Oracle 12c, the IDENTITY column offers a simpler, native solution for auto-incrementing IDs. In this blog, we’ll learn about how to use the IDENTITY column effectively with syntax, examples, and comparisons to other methods.

Table of Contents:

What is Oracle IDENTITY Column?
How to use the IDENTITY Column on Oracle?
Examples of using the IDENTITY Column on Oracle
IDENTITY Columns Modes: ALWAYS vs BY DEFAULT
Conclusion

What is Oracle IDENTITY Column?

The Oracle IDENTITY column is a feature introduced in Oracle 12c that allows automatic generation of unique, sequential values for a table’s primary key, eliminating the need for manual sequences or triggers. It provides a native way to implement auto-increment in Oracle, making it easier to manage primary keys. If you’re looking for how to create auto increment column in Oracle 12c, the IDENTITY column is the most efficient method. Below, you’ll find an Oracle IDENTITY column example with syntax to help you implement it in your own tables.

How to use the IDENTITY Column on Oracle?

Without manually creating a trigger or function, the IDENTITY method simplifies generating unique, sequential values for a primary key column. The IDENTITY column is available from Oracle 12c onwards and works similarly to AUTO_INCREMENT in MySQL.

Syntax:

CREATE TABLE table_name (
    column_name NUMBER GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
    [ START WITH start_value ]
    [ INCREMENT BY increment_value ]
    [ MINVALUE min_value | NOMINVALUE ]
    [ MAXVALUE max_value | NOMAXVALUE ]
    [ CYCLE | NOCYCLE ]
    [ CACHE cache_size | NOCACHE ]
    PRIMARY KEY,
    ...
);
INSERT INTO table_name (column2, column3, ...) VALUES (value2, value3, ...);
SELECT * FROM table_name;

Examples of using the IDENTITY Column on Oracle

Now, let’s see some examples for using the IDENTITY Column on Oracle for creating an ID with AUTO_INCREMENT.

Example 1:

CREATE TABLE Employees (
    Employee_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    Employee_dept NVARCHAR2(50),
    Salary DECIMAL(10,2)
);
INSERT INTO Employees (Employee_dept, Salary) VALUES ('HR', 50000.00);
INSERT INTO Employees (Employee_dept, Salary) VALUES ('Finance', 60000.50);
INSERT INTO Employees (Employee_dept, Salary) VALUES ('IT', 75000.75);
SELECT * FROM Employees;

Output:

CREATE TABLE with IDENTITY COLUMN Oracle

Explanation: Here, the IDENTITY Column automatically generates unique values for the Employee_ID.

Example 2:

CREATE TABLE Departments (
    Dept_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    Dept_name NVARCHAR2(50),
    Location NVARCHAR2(100)
);
INSERT INTO Departments (Dept_name, Location) VALUES ('HR', 'New York');
INSERT INTO Departments (Dept_name, Location) VALUES ('Finance', 'Los Angeles');
INSERT INTO Departments (Dept_name, Location) VALUES ('IT', 'San Francisco');
SELECT dept_id FROM Departments;

Output:

Explanation: Here, the IDENTITY Column automatically generates unique values for the Dept_ID.

IDENTITY Columns Modes: ALWAYS vs BY DEFAULT

Feature	Generated ALWAYS as IDENTITY	Generated BY DEFAULT as IDENTITY
Value Generation	Oracle always generates the value	Oracle generates the value only if not provided manually
Manual Insertion Allowed?	Not allowed – raises an error if a value is supplied	Allowed – manual value can override the generated one
Use Case	Enforce strict auto-increment behavior	Provide flexibility to override auto-increment values
Error on Manual Value Insert	Yes	No
Common Scenario	Secure systems where ID control must be automatic	Systems requiring occasional manual control of IDs

Conclusion

An easy method for creating auto-incrementing primary key values in Oracle is to use the IDENTITY column. It simplifies database design by doing away with the requirement to create distinct sequences and triggers. It is useful for large-scale applications since it increases speed by simplifying the production of IDs. All things considered, IDENTITY is a cutting-edge and dependable technique for managing Oracle database primary key creation. It also improves speed by reducing the complexity of ID creation, thus being suitable for large-scale applications.