Making the Primary Key Sequential in Oracle SQL in APEX

 A primary key is a unique identifier for each row in a table. To ensure that new records receive a sequential primary key value, you can use an Oracle sequence and a trigger or an identity column. Oracle APEX, which operates on top of an Oracle Database, supports both approaches.

Using a Sequence and a Trigger

A sequence is an Oracle database object that generates unique numbers in a specific order. A trigger automatically assigns the next value from the sequence before an insert operation.

Steps to Create a Sequential Primary Key

1. Create a Table Without Defining the Primary Key as an Identity Column

CREATE TABLE employees (

    id NUMBER PRIMARY KEY,

    first_name VARCHAR2(50),

    last_name VARCHAR2(50),

    email VARCHAR2(100)

);

The id column is the primary key but does not yet have an automatic way to generate values.

2. Create a Sequence to Generate Sequential Values

CREATE SEQUENCE employees_seq

START WITH 1

INCREMENT BY 1

NOCACHE

NOCYCLE;

• START WITH 1: The sequence starts from 1.

• INCREMENT BY 1: Each new value increases by 1.

• NOCACHE: Prevents preallocating values, ensuring sequential assignment.

• NOCYCLE: Ensures the sequence does not restart after reaching the maximum value.

3. Create a Trigger to Automatically Assign the Next Value

CREATE OR REPLACE TRIGGER employees_trigger

BEFORE INSERT ON employees

FOR EACH ROW

BEGIN

    IF :NEW.id IS NULL THEN

        SELECT employees_seq.NEXTVAL INTO :NEW.id FROM dual;

    END IF;

END;

/

• The trigger runs before an INSERT operation.

• If id is NULL, the next sequence value is assigned automatically.

4. Insert Data Without Manually Assigning the Primary Key

INSERT INTO employees (first_name, last_name, email)  

VALUES ('John', 'Doe', 'john.doe@example.com');  

The database automatically assigns the next sequential value to the id column.

5. Verify That the Primary Key Is Incrementing Sequentially

SELECT * FROM employees ORDER BY id;

Each new record will have a sequential id.

Using an Identity Column (Oracle 12c and Later)

For newer versions of Oracle (12c and above), you can use the identity column feature to automatically generate sequential primary keys.

Steps to Create a Table with an Identity Column

1. Create the Table Using an Identity Column

CREATE TABLE employees (

    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

    first_name VARCHAR2(50),

    last_name VARCHAR2(50),

    email VARCHAR2(100)

);

• GENERATED ALWAYS AS IDENTITY: Automatically generates a sequential value for each new row.

• PRIMARY KEY: Ensures uniqueness.

2. Insert Data Without Specifying the Primary Key

INSERT INTO employees (first_name, last_name, email)  

VALUES ('Jane', 'Smith', 'jane.smith@example.com');  

Oracle automatically assigns the next available id value.

3. Check the Assigned Primary Key Values

SELECT * FROM employees ORDER BY id;

The id values will be sequentially increasing.

Choosing Between a Sequence + Trigger vs. Identity Column

Feature	Sequence + Trigger	Identity Column
Oracle Version	Works on all versions	Oracle 12c+
Flexibility	More control over values	Simpler setup
Manual Override	Can insert custom values	Restricted unless BY DEFAULT is used
Performance	Slightly more overhead	More efficient

For new applications running on Oracle 12c or later, using an identity column is the recommended approach. However, if you need to manually control primary key assignment or are working with an older Oracle version, using a sequence and trigger is a reliable alternative.

Additional Examples

The method requires the use of Sequence-

EXAMPLE Method #1- Manually

CREATE SEQUENCE  "CA_AUTH"  MINVALUE 1 MAXVALUE 999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 2 NOORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL ;

UPDATE tableName SET columnName = sequenceName.NEXTVAL

EXAMPLE Method #2 – Via APEX Object Browser

Step -1

Object Browser > Sequence > Create Sequence

Step -2

Completed

Step 3- 

UPDATE tableName SET columnName = sequenceName.NEXTVAL