Oracle IDENTITY Column for Auto-Incrementing IDs

The IDENTITY column is the most widely and efficiently used method to create an ID with AUTO_INCREMENT on Oracle. In this blog, let’s explore about IDENTITY Column in detail.

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:

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.

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.

Other Methods for Creating an ID with AUTO_INCREMENT on Oracle

• Using a SEQUENCE and TRIGGER on Oracle SQL
• Using a SEQUENCE With DEFAULT on Oracle SQL
• Using a BEFORE INSERT Trigger with MAX(ID) on Oracle SQL
• Using a GUID on Oracle SQL
• Using a SEQUENCE with an INSERT Statement Directly on Oracle SQL
• Using a Combination of SEQUENCE and GUID on Oracle SQL
• Using a BEFORE INSERT TRIGGER with Custom Logic on Oracle SQL