#1 Spot for Defeating Online Exams

how-to-create-an-auto-increment-id-on-oracle?

“`html

Auto_INCREMENT holds significant value in Oracle for various critical factors, particularly when managing primary keys. Oracle implements this through sequence and identity columns. In this article, we will investigate the process of creating an ID with AUTO_INCREMENT in Oracle SQL.

Table of Contents:

Techniques for implementing AUTO_INCREMENT in Oracle SQL

Prior to delving into the techniques, let’s set up an employee table that will be utilized for all illustrations.

CREATE TABLE Employees (
    Employee_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    Employee_dept NVARCHAR2(50),
    Salary DECIMAL(10,2)
);

Technique 1: Utilizing an IDENTITY Column in Oracle SQL

An IDENTITY column can be established to automatically produce distinct values for the ID field.

Syntax:

-- Creating a table with an IDENTITY Column
CREATE TABLE table_name (
    column_name NUMBER GENERATED  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,
    other_column_name column_type(size),
    ...
);

-- For inserting the data
INSERT INTO table_name (column2, column3, ...) VALUES (value2, value3, ...);

-- To display data from the table 
SELECT * FROM table_name;

Example:

-- Create the employees table with IDENTITY Column
CREATE TABLE Employees (
    Employee_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    Employee_dept NVARCHAR2(50),
    Salary DECIMAL(10,2)
);

-- Inserting data into the Employee table
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);

-- To show the data 
SELECT * FROM Employees;

Output:

Mastering AUTO_INCREMENT IDs: A Guide for Oracle Users

Explanation: We utilize GENERATED AS IDENTITY to inherently create unique values for the Employee_id.

Technique 2: Employing a SEQUENCE and TRIGGER in Oracle SQL

A sequence is a SQL object within the Oracle database that produces unique numeric values sequentially. It serves as a substitute for auto-increment.

Syntax:

-- Creating a table
CREATE TABLE table_name (
    column_name NUMBER PRIMARY KEY,
    other_column_name column_type(size),
    ...
);

-- Create a Sequence
CREATE SEQUENCE sequence_name  
START WITH start_value  
INCREMENT BY increment_value  
[ MINVALUE min_value | NOMINVALUE ]  
[ MAXVALUE max_value | NOMAXVALUE ]  
[ CYCLE | NOCYCLE ]  
[ CACHE cache_size | NOCACHE ];

-- Create a TRIGGER to AUTO-Assign ID
CREATE OR REPLACE TRIGGER trigger_name  
BEFORE INSERT ON table_name  
FOR EACH ROW  
BEGIN  
    IF :NEW.column_name IS NULL THEN  
        SELECT sequence_name.NEXTVAL INTO :NEW.column_name FROM dual;  
    END IF;  
END;
/
-- Insert data into the table
INSERT INTO table_name (column2, column3, ...) VALUES (value2, value3, ...);
-- Display data from the table

Example:

-- Create the Employee table
CREATE TABLE Employees (
    Employee_id NUMBER PRIMARY KEY,
    Employee_dept NVARCHAR2(50),
    Salary DECIMAL(10,2)
);

-- Create a sequence for Employee_id
CREATE SEQUENCE employee_seq  
START WITH 1  
INCREMENT BY 1  
NOCACHE  
NOCYCLE;

-- Create a trigger for Employee_id
CREATE OR REPLACE TRIGGER employees_before_insert  
BEFORE INSERT ON Employees  
FOR EACH ROW  
BEGIN  
    IF :NEW.Employee_id IS NULL THEN  
        SELECT employee_seq.NEXTVAL INTO :NEW.Employee_id FROM dual;  
    END IF;  
END;
/
-- Inserting data into Employees table 
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);

-- Viewing the Employee table 
SELECT * FROM Employees;

Output:

Mastering AUTO_INCREMENT IDs: A Guide for Oracle Users

Explanation: The trigger automatically assigns the Employee_ID value from Employee_seq.

Technique 3: Utilizing a SEQUENCE With DEFAULT in Oracle SQL

This approach combines a SEQUENCE with a DEFAULT Keyword, eliminating the necessity for a trigger.

Syntax:

``````html
class="wp-block-code">-- Construct a table 
CREATE TABLE table_name (
    column_name NUMBER DEFAULT sequence_name.NEXTVAL PRIMARY KEY,
    other_column_name column_type(size),
    ...
);

-- For establishing a SEQUENCE
CREATE SEQUENCE sequence_name  
START WITH start_value  
INCREMENT BY increment_value  
[ MINVALUE min_value | NOMINVALUE ]  
[ MAXVALUE max_value | NOMAXVALUE ]  
[ CYCLE | NOCYCLE ]  
[ CACHE cache_size | NOCACHE ];

--Inserting entries into the table 
INSERT INTO table_name (column2, column3, ...) VALUES (value2, value3, ...);

-- To retrieve the data
SELECT * FROM table_name;

Example:

--This sequence generates distinct Employee_id values automatically
CREATE SEQUENCE employee_seq  
START WITH 1  
INCREMENT BY 1  
NOCYCLE  
NOCACHE;

-- Construct the Employees table 
CREATE TABLE Employees (
    Employee_id NUMBER DEFAULT employee_seq.NEXTVAL PRIMARY KEY,
    Employee_dept NVARCHAR2(50),
    Salary DECIMAL(10,2)
);

--Inserting entries into Employees table
INSERT INTO Employees (Employee_dept, Salary) VALUES ('BDT', 55000.00);
INSERT INTO Employees (Employee_dept, Salary) VALUES ('TRA', 33000.00);
INSERT INTO Employees (Employee_dept, Salary) VALUES ('TCW', 75000.00);

--To retrieve the table 
SELECT * FROM Employees;

Output:

Mastering AUTO_INCREMENT IDs: A Guide for Oracle Users

Explanation: The sequence generates distinctive EMPLOYEE_ID values automatically.

Method 4: Utilizing a BEFORE INSERT Trigger with MAX(ID) on Oracle SQL

This approach produces an AUTO_INCREMENT ID by identifying the highest existing ID and incrementing it by one.

Syntax:

--Construct an Employee table
CREATE TABLE table_name (
    column_name NUMBER PRIMARY KEY,
    other_column_name column_type(size),
    ...
);

--Establish a BEFORE INSERT Trigger
CREATE OR REPLACE TRIGGER trigger_name  
BEFORE INSERT ON table_name  
FOR EACH ROW  
BEGIN  
    IF :NEW.column_name IS NULL THEN  
        SELECT COALESCE(MAX(column_name), 0) + 1 INTO :NEW.column_name FROM table_name;
    END IF;  
END;
/
--Insert the entries into the table 
INSERT INTO table_name (column2, column3, ...) VALUES (value2, value3, ...);

--To retrieve the data
SELECT * FROM table_name;

Example:

-- Construct an Employee table 
CREATE TABLE Employees (
    Employee_id NUMBER PRIMARY KEY,
    Employee_dept NVARCHAR2(50),
    Salary DECIMAL(10,2)
);

--Establish a BEFORE INSERT TRIGGER
CREATE OR REPLACE TRIGGER employees_before_insert  
BEFORE INSERT ON Employees  
FOR EACH ROW  
BEGIN  
    IF :NEW.Employee_id IS NULL THEN  
        SELECT COALESCE(MAX(Employee_id), 0) + 1 INTO :NEW.Employee_id FROM Employees;
    END IF;  
END;
/
--Insert some values
INSERT INTO Employees (Employee_dept, Salary) VALUES ('BDT', 55000.00);
INSERT INTO Employees (Employee_dept, Salary) VALUES ('TRA', 33000.00);
INSERT INTO Employees (Employee_dept, Salary) VALUES ('TCW', 75000.00);

--To retrieve the table
SELECT * FROM Employees;

Output:

Mastering AUTO_INCREMENT IDs: A Guide for Oracle Users

Explanation: This TRIGGER automatically assigns an EMPLOYEE_ID.

Method 5: Implementing a GUID on Oracle SQL

A GUID (Globally Unique Identifier) serves as a unique identifier utilized to guarantee uniqueness across tables, databases, and distributed systems.

Syntax:

-- Construct a table with GUID as the primary key
CREATE TABLE table_name (
    column_name RAW(16) DEFAULT SYS_GUID() PRIMARY KEY,
    other_column_name column_type(size),
    ...
);

-- Insert the entries into the table
INSERT INTO table_name (column2, column3, ...) VALUES (value2, value3, ...);

-- To retrieve the data 
SELECT RAWTOHEX(column_name), other_column_name FROM table_name;

Example:

-- Construct a table
CREATE TABLE Employees (
    Employee_id RAW(16) DEFAULT SYS_GUID() PRIMARY KEY,
    Employee_dept NVARCHAR2(50),
    Salary DECIMAL(10,2)
);

--Insert the entries
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);

--To retrieve the data
SELECT RAWTOHEX(Employee_id) AS Employee_ID, Employee_dept, Salary FROM Employees;

Output:

Mastering AUTO_INCREMENT IDs: A Guide for Oracle Users

Explanation: Each record automatically receives a unique GUID dubbed Employee_id. As Employee_id is stored as RAW(16), we utilize RAWTOHEX(Employee_ID) to convert it into a readable style.

Method 6: Employing a SEQUENCE with INSERT Statement Directly on Oracle SQL

A Sequence in Oracle SQL is a database object designed to generate unique numeric values, predominantly for primary keys

Syntax:

--We establish a distinct sequence and utilize it manually in the INSERT Statements
CREATE SEQUENCE sequence_name
START WITH initial_value  
INCREMENT BY step_value  
[optional parameters];  
CREATE TABLE table_name (
    column_name NUMBER PRIMARY KEY,
    other_column_name column_type(size),
    ...
);

--Inserting records using SEQUENCE
INSERT INTO table_name (column1, column2, column3, ...) 
VALUES (sequence_name.NEXTVAL, value2, value3, ...);

--To retrieve the data 
SELECT * FROM table_name;

Example:

--To establish a distinct sequence
CREATE SEQUENCE employee_seq
START WITH 1 
INCREMENT BY 1 
NOCYCLE NOCACHE;
CREATE TABLE Employees (
    Employee_id NUMBER PRIMARY KEY,
    Employee_dept NVARCHAR2(50),
    Salary DECIMAL(10,2)
);

--Insert into the table
INSERT INTO Employees (Employee_id, Employee_dept, Salary)  
VALUES (employee_seq.NEXTVAL, 'HR', 50000.00);
INSERT INTO Employees (Employee_id, Employee_dept, Salary)  
VALUES (employee_seq.NEXTVAL, 'Finance', 60000.50);
INSERT INTO Employees (Employee_id, Employee_dept, Salary)  
VALUES (employee_seq.NEXTVAL, 'IT', 75000.75);

--To retrieve the table
SELECT * FROM Employees;

Output:

Mastering AUTO_INCREMENT IDs: A Guide for Oracle Users

Explanation: Employee_seq produces unique numeric IDs. The Employee_id is not automatically generated, allowing us to manually assign values utilizing the sequence.

Method 7: Implementing a Combination of SEQUENCE and GUID on Oracle SQL

This method merges the numeric SEQUENCE-based ID with a GUID to ensure both local indexing efficiency and global exclusivity.

Syntax:

--Create a sequence
CREATE SEQUENCE sequence_name
START...
``````html
WITH starting_value  
INCREMENT BY increment_value  
[optional parameters];  
CREATE TABLE table_identifier (
    field1 NUMBER DEFAULT sequence_identifier.NEXTVAL PRIMARY KEY,  
    field2 RAW(16) DEFAULT SYS_GUID(),  
    field3 field_type(size),
    ...
);

--Add data to a table
INSERT INTO table_identifier (field3, field4, ...)  
VALUES (data3, data4, ...);

--To view the table
SELECT field1, RAWTOHEX(field2), field3 FROM table_identifier;

Illustration:

--Establish an Employee table with SEQUENCE and GUID
CREATE SEQUENCE employee_sequence
START WITH 1 
INCREMENT BY 1 
NOCYCLE NOCACHE;
CREATE TABLE Employees (
    Employee_id NUMBER DEFAULT employee_sequence.NEXTVAL PRIMARY KEY,
    Employee_uuid RAW(16) DEFAULT SYS_GUID(),
    Employee_dept NVARCHAR2(50),
    Salary DECIMAL(10,2)
);

--Adding data into a table
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);

--View data from the employee table
SELECT  RAWTOHEX(Employee_uuid) AS Employee_UUID, Salary FROM Employees;

Result:

Mastering AUTO_INCREMENT IDs: A Guide for Oracle Users

Clarification: A SEQUENCE is employed for numeric identifiers (Employee_id). A GUID (RAW(16)) column is allocated for globally unique identifiers.

Approach 8: Utilizing a BEFORE INSERT TRIGGER with Tailored Logic on Oracle SQL

This approach implements a BEFORE INSERT TRIGGER to automatically assign employee_id based on the highest current Employee_id (MAX(Employee_id)) and the subsequent value from a sequence (employee_sequence.NEXTVAL).

Syntax:

CREATE SEQUENCE sequence_identifier
START WITH starting_value  
INCREMENT BY increment_value  
[optional parameters];
CREATE TABLE table_identifier (
    field1 NUMBER PRIMARY KEY,  
    field2 field_type(size),
    ...
);

--Establish BEFORE INSERT TRIGGER
CREATE OR REPLACE TRIGGER trigger_identifier  
BEFORE INSERT ON table_identifier  
FOR EACH ROW  
BEGIN  
    IF :NEW.field1 IS NULL THEN  
        SELECT COALESCE(MAX(field1), sequence_identifier.NEXTVAL) + 1  
        INTO :NEW.field1  
        FROM table_identifier;
    END IF;  
END;
/
--Add the data  
INSERT INTO table_identifier (field2, field3, ...)  
VALUES (data2, data3, ...);

--View the table  
SELECT * FROM table_identifier;

Illustration:

-- Step 1: Establish a Sequence for Employee ID
CREATE SEQUENCE employee_sequence
START WITH 1 
INCREMENT BY 1 
NOCYCLE NOCACHE;

-- Step 2: Create the Employees Table
CREATE TABLE Employees (
    Employee_id NUMBER PRIMARY KEY,
    Employee_dept NVARCHAR2(50),
    Salary DECIMAL(10,2)
);

-- Step 3: Set up a BEFORE INSERT Trigger for Auto-Increment
CREATE OR REPLACE TRIGGER employees_before_insert  
BEFORE INSERT ON Employees  
FOR EACH ROW  
DECLARE
    v_max_id NUMBER;
BEGIN  

    -- Verify the highest Employee_id in case the sequence is lagging
    SELECT COALESCE(MAX(Employee_id), 0) INTO v_max_id FROM Employees;

    -- Assign the subsequent ID (greater of max ID or sequence nextval)
    :NEW.Employee_id := GREATEST(v_max_id + 1, employee_sequence.NEXTVAL);
END;
/
-- Step 4: Insert Data (Trigger will auto-generate Employee_id)
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);

-- Step 5: Review Data
SELECT * FROM Employees;

Result:

Mastering AUTO_INCREMENT IDs: A Guide for Oracle Users

Clarification: A SEQUENCE is utilized to produce numeric identifiers. A BEFORE INSERT TRIGGER assigns the Employee_id.

Performance Assessment of AUTO_INCREMENT Techniques on Oracle SQL

Technique Application Scenario Advantages Disadvantages
IDENTITY Column For basic auto-increment needs No requirement for triggers or sequences Potential gaps if transactions are reverted. Yet, gaps are lessened with NOCACHE and INCREMENT BY 1
SEQUENCE and TRIGGER Suitable for legacy frameworks Handles manual ID adjustments Performance cost due to additional PL/SQL execution 
SEQUENCE with DEFAULT Optimal for Oracle 12c+ without needing triggers  Greatest efficiency post IDENTITY column Demands manual gap management
BEFORE INSERT Trigger with MAX(ID) Beneficial for smaller tables where maintaining ID continuity is crucial Averts sequence gaps May lead to locking challenges during high concurrency
GUID Ideal for distributed systems Guarantees uniqueness across different databases Requires additional storage capacity
SEQUENCE with INSERT Statement Directly Best when sequences are vital but auto-increment isn’t enforced No triggers necessary Mandates explicit use in each INSERT operation
SEQUENCE + GUID Optimal for hybrid scenarios Avoids ID conflicts in distributed contexts Extra storage space is required
BEFORE INSERT Trigger with Tailored Logic For intricate cases, where a complex ID is requisite. Capable of executing custom regulations Tailored logic could impede insert performance

Practical Illustrations

1. E-Commerce framework: In an e-commerce setup, we require the creation of a customer table where each individual receives an automatically assigned unique ID.

Illustration:

-- Create a sequence
CREATE SEQUENCE customer_sequence START WITH 1 INCREMENT BY 1;

-- Establish the Customers table
CREATE TABLE CUSTOMERS (
    CUSTOMER_ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(100),
    EMAIL VARCHAR2(100) UNIQUE
);

-- Create a trigger to auto-increment CUSTOMER_ID
CREATE OR REPLACE TRIGGER customer_trigger 
BEFORE INSERT ON CUSTOMERS 
FOR EACH ROW 
BEGIN 
    SELECT customer_sequence.NEXTVAL INTO :NEW.CUSTOMER_ID FROM DUAL; 
END;
/
INSERT INTO CUSTOMERS (NAME, EMAIL) VALUES ('Alice Johnson', '[email protected]');
INSERT INTO CUSTOMERS (NAME, EMAIL) VALUES ('Bob Smith', '[email protected]');
COMMIT;

--To view the table 
SELECT CUSTOMER_ID, name FROM CUSTOMERS;

Result:

Mastering AUTO_INCREMENT IDs: A Guide for Oracle Users

Clarification: customer_sequence is a sequence that begins at 1 and increases by 1 for each new entry. This sequence will facilitate the automatic generation of unique customer_id entries.

2. Human Resources System: In an HR system, it is necessary to maintain employee records with automatically incrementing employee IDs.

Illustration:

CREATE TABLE EMPLOYEES (
    EMPLOYEE_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    FULL_NAME VARCHAR2(100),
    SALARY 
``````sql
 NUMBER(10,2)
);

--Insert some entries into it
INSERT INTO EMPLOYEES (FULL_NAME, SALARY) VALUES ('John Doe', 50000);
INSERT INTO EMPLOYEES (FULL_NAME, SALARY) VALUES ('Jane Smith', 60000);
COMMIT;

--To showcase the table
SELECT EMPLOYEE_ID, SALARY FROM EMPLOYEES;

Output :

Mastering AUTO_INCREMENT IDs: A Guide for Oracle Users

Explanation: The GENERATED ALWAYS AS IDENTITY clause facilitates the auto-incrementing of Employee_ID, akin to AUTO_INCREMENT in MYSQL. Oracle SQL autonomously produces unique sequential values for the columns.

3. Sales System: Within the sales system, we monitor orders utilizing an automatically produced ORDER_ID.

Example:

-- Establish a sequence
CREATE SEQUENCE order_seq START WITH 1000 INCREMENT BY 1;

-- Set up the Orders table
CREATE TABLE ORDERS (
    ORDER_ID NUMBER PRIMARY KEY,
    CUSTOMER_NAME VARCHAR2(100),
    ORDER_DATE DATE DEFAULT SYSDATE
);

-- Develop a trigger to auto-increment ORDER_ID
CREATE OR REPLACE TRIGGER order_trigger 
BEFORE INSERT ON ORDERS 
FOR EACH ROW 
BEGIN 
    SELECT order_seq.NEXTVAL INTO :NEW.ORDER_ID FROM DUAL; 
END;
/
--Insert some records into the table
INSERT INTO ORDERS (CUSTOMER_NAME) VALUES ('Charlie Brown');
INSERT INTO ORDERS (CUSTOMER_NAME) VALUES ('Lucy Van Pelt');
COMMIT;

--To retrieve data from the table
SELECT ORDER_ID, ORDER_DATE FROM ORDERS;

Output:

Mastering AUTO_INCREMENT IDs: A Guide for Oracle Users

Explanation: Sequence order_seq: Establishes a sequence labeled order_seq that will generate distinct numbers. Each time the sequence is accessed, the subsequent number will increment by 1.

Conclusion

You can establish an ID with AUTO_INCREMENT in Oracle using sequence, identity columns, GUID, etc. These approaches automatically enhance the ID based on the specified parameters. Select the method according to the requirements and use cases outlined in the performance comparison chart. Grasping these strategies enables you to effectively AUTO_INCREMENT the ID column in Oracle.

FAQs

The article How to Create an AUTO_INCREMENT ID on Oracle? appeared first on Intellipaat Blog.

“`

Staff SEO Expert

Leave a Reply

Your email address will not be published. Required fields are marked *

Share This